Today I was shifting one of my sites to another server, and had to rewrite a bunch of code to comply with the software installed on the new server. In the process, I found the database code was flawed to begin with.
I had a table that used an auto-incrementing ID field:
| ID | Data |
| 1 | Stuff 1 |
| 2 | Stuff 2 |
Well, you get the idea. The table has about 100 rows at the moment, and I split the data up into pages of 10 entries displayed on a webpage using PHP. A while ago, when I was first looking at optimizing SQL queries, it became apparent that one should seek to limit the result set to what we want. This is as opposed to returning everything from the SQL server, and filtering the result set in PHP. If we can limit the result set to begin with, we save load on the SQL server, and save time in processing in PHP.
So the idea was just use the ID as the row number. So if we want 10 entries per page, and we are on page 3, then we want entries 31-40 inclusive. So the statement can look something like:
SELECT Data FROM mydb.mytable WHERE ID>30 AND ID<41;
| ID | Data |
| 1 | Stuff 1 |
| 2 | Stuff 2 |
| 4 | Stuff 4 |
| 5 | Stuff 5 |
We’ve lost the row with ID=3! Now if we execute the above style query to get the first 10 rows, we will actually only get 9!
Lets go back to what I was originally trying to do though. I want to select a number of rows with an arbitrary starting row number. MySQL already provides a function for this! For the first 10 rows:
SELECT Data FROM mydb.mytable LIMIT 0,10
And the abstract version of this is:
SELECT (col1,col2,col3…) FROM (database).(table) LIMIT (row number to start from),(number of rows to select)