Buy Percona ServicesBuy Now!

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

MySQL ORDER BY LIMIT Performance Optimization

 | September 1, 2006 |  Posted In: Insight for Developers

Suboptimal MySQL ORDER BY  implementation, especially together with LIMIT is often the cause of MySQL performance problems. Here is what you need to know about MySQL ORDER BY LIMIT optimization to avoid these problems. MySQL ORDER BY with LIMIT is most common use of ORDER BY in interactive applications with large data sets being sorted. On many […]

Read More

MySQL: Followup on UNION for query optimization, Query profiling

 | August 14, 2006 |  Posted In: Insight for Developers

Few days ago I wrote an article about using UNION to implement loose index scan. First I should mention double IN also works same way so you do not have to use the union. So changing query to:

So as you see there are really different types of ranges in MySQL. IN range allows […]

Read More

Extended EXPLAIN

 | July 24, 2006 |  Posted In: Insight for Developers

One nice feature added for EXPLAIN statement in MySQL 4.1 is EXTENDED keyword which provides you with some helpful additional information on query optimization. It should be used together with SHOW WARNINGS to get information about how query looks after transformation as well as what other notes optimizer may wish to tell us. It is […]

Read More

MySQL EXPLAIN limits and errors.

 | July 24, 2006 |  Posted In: Insight for Developers

Running EXPLAIN for problematic queries is very powerful tool for MySQL Performance optimization. If you’ve been using this tool a lot you probably noticed it is not always provide adequate information. Here is list of things you may wish to watch out. EXPLAIN can be wrong – this does not happen very often but it […]

Read More

Indexes in MySQL

 | June 2, 2006 |  Posted In: Insight for DBAs

MySQL does not always make a right decision about indexes usage. Condsider a simple table:

; 250001 (V1)

; 83036 (V2) (execution time = 110 ms) That is index selectivity by condition (ID1=1) is V2/V1 = 0.3321 or 33.21% It is said (e.g. book “SQL Tuning”) if selectivity over 20% then a full table […]

Read More