Advanced MySQL Query Tuning | Web & PHP Magazine

CREATE TABLE City (
    ID int(11) NOT NULL AUTO_INCREMENT,
    Name char(35) NOT NULL DEFAULT '',
    CountryCode char(3) NOT NULL DEFAULT '',
    District char(20) NOT NULL DEFAULT '',
    Population int(11) NOT NULL DEFAULT '0',
    PRIMARY KEY (ID),
    KEY CountryCode (CountryCode)
) Engine=InnoDB;

EXPLAIN select * from City where Name = 'London'\G
alter table City add key (Name);
Index usages

MySQL will choose only 1 index per query (and per table if the query joins multiple tables). In some cases MySQL can also intersect indexes. MySQL uses index statistics to make a decision about the best possible index.

Combined Indexes
Combined indexes are very important for MySQL query optimizations. MySQL can use leftmost part of any index. For example, if we have this index:

Comb(CountryCode, District, Population)
Then MySQL can use:

CountryCode only part
CountryCode + District
CountryCode + District + Population
However, if the query does not have the first leftmost part of an index, MySQL will not be able to use it:
mysql> explain select * from City where
District = 'California' and population > 10000\G

Covered index

The covered index is an index that covers all fields in the query 

Extra: Using where; Using index

“Using index” in the extra field of the explain output means that MySQL will use our covered index. That also means that MySQL will use an index only to satisfy the query: all the information that MySQL needs is in the index. That is usually much faster, especially if we have a large text or blob fields in the table.

Order of the fields in index

The order of the fields in the index is very important. The way b-tree works, it is more beneficial to have a field which will be used for “equality” comparison first and the fields with “range” (more than and less than comparison) second.

Explain select max(DepDelayMinutes), carrier, dayofweek from ontime_2012  where dayofweek = 7 group by carrier

MySQL does not use any indexes (no proper indexes are available), but it also shows “Using temporary; Using filesort”. MySQL will need to create a temporary table to satisfy the “Group by” clause if there is no appropriate index.
alter table ontime_2012 add key covered(dayofweek, Carrier, DepDelayMinutes);
Explain select max(DepDelayMinutes), carrier, dayofweek from ontime_2012 where dayofweek = 7 group by carrier

As we can see from the explain, MySQL will use our index and will avoid creating a temporary table. This is the fastest possible solution. 
ORDER BY and filesort
MySQL may have to perform a “filesort” operation when a query uses the “order by” clause.
To optimize this query we can use a combined index:
alter table City add key my_sort2 (CountryCode, population);
explain select district, name, population from City where CountryCode = 'USA' order by population desc limit 10\G

MySQL was able to use our combined index to avoid sorting: as the index is sorted, MySQL was able to read the index leafs in the correct order.

Using “ORDER BY” + limit optimization can help optimize your queries.

• Covered index is a great MySQL feature and, in most cases, can increase MySQL performance significantly.
• Some queries may also be optimized with a separate index which will enable loose index scan algorithm.
• Order by optimizations can be done with covered index and with the “order by+limit” index technique, described above.

Read full article from Advanced MySQL Query Tuning | Web & PHP Magazine