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,
1 |
select bla from `user` where lastname = 'asdf' |
it is a good idea to create an index for column “lastname”.
Using this query,
1 |
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,
1 2 3 4 |
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.
Comments