Select as little data as possible

Consider these two queries:

SELECT * FROM usertable WHERE Username = 'TelRev';
SELECT ID FROM usertable WHERE Username = 'TelRev' LIMIT 1;

Usertable, we shall assume, contains thousands of rows, with one for each user. The programmer is looking to extract the ID of a user based upon a username, and above are two ways it can be done: select every field from each row and match Username against "TelRev", or to select just the ID field and match Username against "TelRev", and only return one row. I made a simple benchmark to test the exact speed difference between the queries - with 200,000 users (a large number to make the point clear), the first query took 1.31 seconds to execute, whereas the second query took 0.66 seconds to execute - this would have been a much greater difference if TelRev were nearer the start of the table.

The reason for the big speed up for the second query is two-fold: firstly, we're not bothering to return anything other than ID, whereas there could be dozens of fields being returned in the first query. Selecting as little data as possible means using "SELECT *" rarely if ever.

Secondly, and most importantly, the LIMIT 1 at the end of the second query will force MySQL to only return one row and, as we don't have any complicated ordering going on, MySQL will simply stop searching as soon as it finds the first row with Username "TelRev". That is quite clearly superior to the first solution, where MySQL will keep on searching the database to find other TelRev rows - this is rarely the desired situation.

One other advantage to selecting as little data as possible is that it does not clog up your MySQL query cache with useless data.

Want to learn PHP 7?

Hacking with PHP has been fully updated for PHP 7, and is now available as a downloadable PDF. Get over 1200 pages of hands-on PHP learning today!

If this was helpful, please take a moment to tell others about Hacking with PHP by tweeting about it!

Next chapter: Use shorter queries where possible >>

Previous chapter: Optimise your tables

Jump to:

Home: Table of Contents