Optimize MySQL Query Performance with Explicitly Named Fields

By Vikram Vaswami on September 28, 2015


It’s common to see queries like these:

SELECT (*) FROM airport;
SELECT COUNT(*) FROM airport;

These queries use the asterisk (*) wildcard for convenience. However, this convenience comes at a price: The * wildcard forces MySQL to read every field or record in the table, adding to the overall query processing time. To avoid this, explicitly name the output fields you wish to see in the result set, as shown:

SELECT AirportID FROM airport;
SELECT COUNT(AirportID) FROM airport;

In a similar vein, when using subqueries with a WHERE or HAVING clause, it’s also a good idea to be as specific as possible in the WHERE or HAVING clause to reduce the size of the result set that needs to be processed by the outer query. If you’re using MySQL from a client application over TCP/IP, following these simple rules will also reduce the size of the result set that is transmitted to the client by the server, reducing bandwidth consumption and improving performance.

Related Posts

Leave a Reply