I am trying to retrieve data from several tables in an incremental
fashion. IE: getting values > 3 limit 60. I am using the following line:
select icpr.prod_no, icpl.unit_available, iprc.retail where icpr.prod_no >
'04' AND icpr.prod_no=icpl.lprod_no AND icpr.prod_no=iprc.prod_no ORDER BY
icpr.prod_no asc LIMIT 60.
This works very fast. However if I change the icpr.prod_no > '04' to say
'6' so where icpr.prod_no > '6'. It is extremely slow. The explain says:
Merge Join
-> Merge Join
-> Index Scan using iprc_prod_no on iprc
-> Index Scan using icpr_prod_on on icpr
-> Index Scan using icpl_lprod_no on icpl
However if I change the query to have an upper limit.
select icpr.prod_no, icpl.unit_available, iprc.retail where icpr.prod_no >
'6' AND icpr.prod_no < '7' AND icpr.prod_no=icpl.lprod_no AND
icpr.prod_no=iprc.prod_no ORDER BY icpr.prod_no asc LIMIT 60.
It runs very fast, with the explain saying.
Nested Loop
-> Nested Loop
->Index Scan using icpr_prod_no on icpr
->Index Scan using iprc_prod_no on iprc
-> Index Scan icpl_lprod_no on icpl
My problem comes into that I don't want to hard core an upper limit,
because I really don't know what it could be. Anyone have an suggestions
on what I can do?
Thanks,
Mike