PS
I wish the next MySQL update would have a function for FOUND ROWS w/o having to issue the CALC FOUND ROWS clause.. that would really simplify things so much more..
Indeed, there are a number of differences between PHP, mysqli/PDO, MySQL and ASP,ADO,Microsoft SQL Server, with which I'm more familiar, that have me wondering whether I'm doing the things the right/best way.
For example in ASP/ADO/ MS SQL if you wanted to retrieve data to use in a drop-down, or you wanted a limited number of records for page, similar to what you'd get with LIMIT, you would use GETROWS(): http://www.adopenstatic.com/experiments/recordsetpaging.asp which allows you to fetch a whole result set into a 2 dimensional array. This was generally considered more efficient than the more common way of returning a recordset then looping through the results outputting the values at each step of the loop because there are a lot less calls to the DB and you close the connection earlier:
Looping through recordset:
Open connection to DB.
SELECT Statement
WHILE NOT End of recordset
Output row result
WEND
Close connection
Using GetRows():
Open connection to DB.
SELECT statement
Fetch all the results into array
Close connection
Loop through array
I've tried to mimic this with PHP, PDO, MySQL using fetchall: http://php.net/manual/en/pdostatement.fetchall.php and that part is probably OK, because I'm only using it to fetch tens of records, not hundreds or thousands. Where it falls down is that using LIMIT with a start position incurs a cumulative performance hit, whereas ADO GETROWS() used in conjunction with Move() doesn't. I can find no equivalent to Move(), which is a shame, because if we could drop the start position from LIMIT and use it in conjunction with Move() we could forget all this nonsense about constructing WHERE clauses to determine where we left off and keep things like direct page navigation.
Unless of course anyone know different. 😉