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.