13.3.1 Optimizing Queries by Limiting Output - MySQL

Some optimizations can be done independently of whether indexes are used. A simple but effective technique is to reduce the amount of output a query produces.

One way to eliminate unnecessary output is by using a LIMIT clause. If you don't need the entire result set, specify how many rows the server should return by including LIMIT in your query. This helps two ways:

• Less information need be returned over the network to the client.

• In many cases, LIMIT allows the server to terminate query processing earlier than it would otherwise.

Another way to limit query output is by selecting only the columns you need, rather than using SELECT * to retrieve all columns. Suppose you want information about countries having names that begin with M. The following query produces that information:

SELECT * FROM Country WHERE Name LIKE 'M%';

However, if all you really want to know is the country names, don't write the query like that. Most of the information retrieved will be irrelevant to what you want to know, resulting in unnecessary server effort and network traffic. Instead, select only the Name column:

SELECT Name FROM Country WHERE Name LIKE 'M%';

The second query is faster because MySQL has to return less information to the client when you select just one column rather than all of them.

In addition, if an index on Name exists, you get even more improvement for two reasons:

• The index can be used to determine quickly which Name values satisfy the condition in the WHERE clause. This is faster than scanning the entire table.

• Depending on the storage engine, the server might not read the table rows at all. If the values requested by the query are in the index, by reading the index, MySQL already has the information that the client wants. For example, the MyISAM handler will read the index file to determine which values satisfy the query, and then return them to the client without reading the datafile at all. Doing so is faster than reading both the index file and the datafile.