Re: LIMIT result for paging
Posted by:
Rick James
Date: December 02, 2014 09:52AM
> is there a more lean way than to add : JOIN `products` T3 ON T2.catalogID = T3.catalogID
No, that is what is needed. Yes, you are hitting `products` twice, but that is OK.
> I want to retriewe the next 10 matching IDs
> LIMIT 0, 10
-->
LIMIT 10, 10 -- next 10
LIMIT 20, 10 -- the third set of 10.
OFFSET 20 LIMIT 10 -- same as LIMIT 20, 10
> or as many matches as there is within the entire RecordSet if less than 10
LIMIT xx, 10 -- will stop short if there are less than 10.
If you are asking how to avoid putting "Next" and/or "Prev" buttons on the web page, that is a different question. I like to LIMIT 11, display 10 (or fewer), then see if there were 11 rows returned. If 11, then present a Next button, else don't. Clean UI (avoids "Next" going to an empty page); reasonably efficient (LIMIT 11 is almost as fast as LIMIT 10); etc.
> SELECT * FROM
-->
SELECT T1.*, T3.*
(to avoid the extra T2.ID column)
Or... Spell out the individual columns.