Select Page
This entry has been published on 2015-11-04 and may be out of date.

Last Updated on 2015-11-04.

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,

select bla from `user` where lastname = 'asdf'

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

 

Using this query,

select max(logindate) from `user` where lastname = 'asdf'

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,

select a.asdf, b.bsdf, c.csdf
from a
left join a.id on b.aid
left join a.id on c.aid

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