EmergencyEMERGENCY? Get 24/7 Help 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

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

Covering index and prefix indexes

 | November 23, 2006 |  Posted In: Insight for DBAs

I already wrote in the blog as well mentioned in presentation there is often a choice you have to make between having prefix index – which can be significantly smaller in size and having index being covering index, which means query can be executed using only data from the index without reading the row itself. […]

Read More

Percona’s widely read Percona Data Performance blog highlights our expertise in enterprise-class software, support, consulting and managed services solutions for both MySQL® and MongoDB® across traditional and cloud-based platforms. The decades of experience represented by our consultants is found daily in numerous and relevant blog posts.

Besides specific database help, the blog also provides notices on upcoming events and webinars.
Want to get weekly updates listing the latest blog posts? Subscribe to our blog now! Submit your email address below and we’ll send you an update every Friday at 1pm ET.

No, thank you. Please do not ask me again.