EmergencyEMERGENCY? Get 24/7 Help Now!

MySQL 6.0 vs 5.1 in TPC-H queries

 | March 25, 2008 |  Posted In: Benchmarks

Last week I played with queries from TPC-H benchmarks, particularly comparing MySQL 6.0.4-alpha with 5.1. MySQL 6.0 is interesting here, as there is a lot of new changes in optimizer, which should affect execution plan of TPC-H queries. In reality only two queries (from 22) have significantly better execution time (about them in next posts), […]

Read More

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

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

Redundant index is not always bad

 | August 28, 2007 |  Posted In: Benchmarks, Insight for DBAs

About year ago Peter wrote about redundant indexes and mentioned sometimes it is good to leave two indexes, even one is first part of another. I’m speaking about BTREE indexes, for example, KEY (A), and KEY (A,B). From SQL point of view KEY(A) is not needed, as for queries like WHERE A=5 the index (A,B) […]

Read More

COUNT(*) vs COUNT(col)

 | April 10, 2007 |  Posted In: Insight for Developers

Looking at how people are using COUNT(*) and COUNT(col) it looks like most of them think they are synonyms and just using what they happen to like, while there is substantial difference in performance and even query result. Lets look at the following series of examples:

Read More

Getting MySQL to use full key length

 | January 31, 2007 |  Posted In: Insight for DBAs

There is one bug, or “missing feature” in MySQL Optimizer which may give you hard time causing performance problems which may be hard to track down, it is using only part of the index when full index can be used or using shorter index while there is longer index available. The last item is yet […]

Read More