Using LIMIT statements in MySQL
To get the front page images, I used a LIMIT function in SQL. something like this:
SELECT * FROM places ORDER BY add_date DESC LIMIT 0,6
And use these results to query the database again and grab the main photo details.
Easy, I thought. They I realised that sometimes a place doesn’t have a photo to go along with it. In my ideal website, every place would have at least one photo, but sometimes. it’s just not possible. This leaves a nasty broken link on the front page, not pretty. What to do in these cases?
In my limited knowledge of SQL, I found that using a LIMIT clause in a SELECT statement is actually pretty inefficient. The LIMIT is the last thing done to the results, so even if you LIMIT a statement which returns lots of records, MySQL still gets every record first, then LIMITs the large set. Knowing this (and also having a very small data set in reality) I figured I might as well just not LIMIT the statement, and use a while loop in PHP to grab the latest 6 which actually do have photos. When I have a large dataset, I can start to think about efficiency.
No Comments Yet