18.2.3 Select as little data as possibleThis is NOT the latest copy of this book; click here for the latest version.
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 see this stuff in print? PHP in a Nutshell takes the core topics covered here, adds in thousands of edits from the editorial team and myself, and combines them to make an unbeatable reference for PHP programmers at all levels.
My latest book has hundreds more tips on how to use PHP, Apache, and MySQL, plus Perl, Python, shell scripts, performance tuning, and more!
|
