Buy Percona ServicesBuy Now!

MySQL Performance – eliminating ORDER BY function

 | October 17, 2007 |  Posted In: Insight for Developers

One of the first rules you would learn about MySQL Performance Optimization is to avoid using functions when comparing constants or order by. Ie use indexed_col=N is good. function(indexed_col)=N is bad because MySQL Typically will be unable to use index on the column even if function is very simple such as arithmetic operation. Same can […]

Read More

Be careful when joining on CONCAT

 | October 16, 2007 |  Posted In: Insight for Developers

The other day I had a case with an awful performance of a rather simple join. It was a join on tb1.vid = CONCAT(‘prefix-‘, tb2.id) with tb1.vid – indexed varchar(100) and tb2.id – int(11) column. No matter what I did – forced it to use key, forced a different join order, it did not want […]

Read More

UNION vs UNION ALL Performance

 | October 5, 2007 |  Posted In: Benchmarks, Insight for Developers

When I was comparing performance of UNION vs MySQL 5.0 index merge algorithm Sinisa pointed out I should be using UNION ALL instead of simple UNION in my benchmarks, and he was right. Numbers would be different but it should not change general point of having optimization of moving LIMIT inside of union clause being […]

Read More

Using VIEW to reduce number of tables used

 | September 28, 2007 |  Posted In: Insight for Developers

Many Open Source software solutions use database per user (or set of tables per user) which starts to cause problems if it is used on massive scale (blog hosting, forum hosting etc), resulting of hundreds of thousands if not millions of tables per server which can become really inefficient. It is especially inefficient with Innodb […]

Read More