EmergencyEMERGENCY? Get 24/7 Help Now!

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

Descending indexing and loose index scan

 | May 9, 2006 |  Posted In: Insight for Developers

Comments to my previous posts, especially this one by Gokhan inspired me to write a bit about descending indexes and about loose index scan, or what Gokhan calls “better range” support. None of these are actially related to Innodb tables in general – these are features MySQL should get for all storage engines at some […]

Read More