Austin Web Development & Database Consulting

Here’s another example of why it’s always a good idea to refer to execution plans and test different query options.  Here’s a query I was running as part of some manual data work:

select ua.* from vn_user_achievements ua
where exists (
select 1 from _users_achievements_set_to_notified m
where ua.user_id = m.user_id
)

Given that this was running under manual data work context, vs. being part of our actual product, I wasn’t too worried about how slow it was performing.  However, before too long I was running this query against bigger data sets and, as a result, I was waiting for over 30 seconds for it to return; that got me curious.  A quick look at it’s execution plan (shown below) revealed 2 big negatives:

1. it wasn’t using an index:
2. it was executing a correlated subquery

As for the index – I knew there was one on vn_user_achievements.user_id, so I was curious why it wasn’t being used.  In addition, correlated subqueries are well known to be painfully slow due to the amount of additional lookups they perform.  So as a next step I decided to rewrite the query using an inner join …

select ua.* from vn_user_achievements ua
inner join _users_achievements_set_to_notified m on ua.user_id = m.user_id

… to see if MySQL would use a smarter execution plan.

It did!  The key and ref columns show that MySQL used the user_id portion of the primary key on vn_user_achievements, and the rewritten query is no longer resulting in a correlated subquery.  As a result, this version returned an identical result set in just 1.5 seconds, or about 20 times faster!

So, if you’re working on tuning a slow query that happens to use exists(), consider rewriting it to use an inner join, especially if the execution plan is dismissing an index you suspect should be used, or if it’s forcing a correlated subquery to be executed.

It’s also worth noting that you need to test any exists() vs. inner join comparisons for identical result sets.  In my case, this wasn’t a problem due to a 1:many relationship, but if the data was many:many, the inner join rewrite would have resulted in a different result set.

I was recently asked about whether column order matters in composite indexes, and if so, to what extent.  I recall this being the case when working with SQL Server 2000 and 2005, so I became curious how MySQL handled scenarios where a query touched different columns in a composite index.  The short answer is yes, column order does matter.  If you’re interested in seeing how, and when, follow along.

Before running some tests I loaded test data, chose 1 table, and ran a baseline query with no indexes on the table (except for it’s primary key). The data I used is one table from one of MySQL’s sample databases, sakila.  You can download the table’s dump here. It’s also worth noting that the table uses the InnoDB storage engine.

Next, I ran the following query to ensure no indexes were being used for my test query. It’s execution plan is below to confirm this, indicated by the key column being null:

select first_name, last_name, last_update
from actor
where last_name = 'Witherspoon'
and first_name = 'Angela'

This is where the useful tests start.  First, I added an index on last_name and first_name:

create index IX_actor_last_name_first_name on
    actor (last_name, first_name)

Next, I re-ran EXPLAIN on the baseline query above.  This time, you can see that the new index on last_name and first_name is being used (again, by referencing the key column):

With use of the index established, I moved on to test dependence on column order. I looked at 3 scenarios, each illustrated with an execution plan.

The first involved modifying the where clause such that it only references last_name.  In this case the index can still be used since last_name is in the left-most position of the index:

select first_name, last_name, last_update
from actor
where last_name = 'Witherspoon'

The second involved modifying the where clause such that it only referenced first_name.  In this case, you can see the index is not used. It’s worth noting that in this case, SQL Server would probably use an index scan operation (vs. an index seek, which is the faster of the 2). It would still leverage the index, but would have to scan into the index to compare against the first_name data since it’s not in the left-most position of the index. Unfortunately, MySQL doesn’t act similar, it dismisses the index entirely.

select first_name, last_name, last_update
from actor
where first_name = 'Angela'

The third test was done out of curiosity, but demonstrates intelligence on the optimizer’s part prior to executing the query.  I modified the where clause such that both columns were included, but the order of the columns cited in the query are opposite of the column order in the index.  As you can see, the order in the where clause makes no difference since the optimizer is smart enough to know which order should be applied to make use of the index.

select first_name, last_name, last_update
from actor
where first_name like 'Angela'
and last_name like 'Witherspoon'

So, all this reinforced 2 things:

1. Index column order does matter
2. Know what’s going on with your queries!  Run EXPLAIN and study execution plans so that you know what your queries are doing vs. possibly making a costly guess.  That relates in this case because indexes aren’t free, and as demonstrated above, there are cases where they’re added but the optimizer simply dismisses them.  In such cases you only make things worse since DML operations actually become slower when operating on columns that are indexed.

Finally, note that all this only covers non-clustered indexes, but the same effect happens on tests I’ve ran against clustered indexes.  Plus, column order in clustered indexes also has another impact on the data itself, which I’ll follow up on in another post that digs into clustered indexes in more detail.