Basic hints for MySQL performance optimization using Indexes

Problem

An SQL query containing multiple table joins, functions etc. is too slow, but necessary.

Solution approaches

Indexes created on single or multiple MySQL table columns can increase performance in a significant way if they are used correctly.

First, have a look at the MySQL Explain command. Simply add “explain ” at the beginning of your query and execute it. E.g. in phpMyAdmin, you get a table which shows which keys (indexes) are available and which ones are used. If you see many NULL values or high row/reference numbers in this table, your table indexes have to get optimized.

 

If you have a query like this,

it is a good idea to create an index for column “lastname”.

 

Using this query,

the index should contain both columns “logindate” and “lastname”. Note, if you create an index “(lastname, logindate)”, a separate index containing only “lastname” is not needed any more.

 

Using multiple joins like,

make sure each of b.aid and c.aid is indexed. Additionally, the joined columns must have the same type and length for the index to be used.

 

 

Reference