MySQL query hint of the day

Today I had a client who had a query something like this;

SELECT `field1`,`field2`,`field3`,`field4` FROM `table_name` WHERE approve='1' LIMIT 0,10 ORDER BY RAND();?

This is exactly how NOT to do a query. For starters it is not indexed which means that it will do a full table search. Besides this the RAND() function then proceeds to randomize EVERY LINE in the table. it only takes a few queries to make this fall flat. At the very least push this to code, generate some random #s and then search for those rows based on an Index.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.