Mysql – How does compound index help speed up query

Here is your original query:

SELECT
    user_actions.*,
    users.twitter_username,
    users.email
FROM
    `user_actions`  
    INNER JOIN users
    ON (user_actions.user_id=users.id)
    ORDER BY timestamp
    DESC LIMIT 0, 30
;

First thing I notice is that you are joining two whole tables. Since you only need twitter_username and email from the users table, you should only join from users using three columns : id, twitter_username and email.

Second thing is the LIMIT clause. It is being executed after the join. You should execute it before the join. In your case, you are requesting for the 30 most recent user actions. If you can guarantee that only 30 rows are retreived from user_actions, the join should operate much faster.

If you read the answer from @DTest, his first two bulletpoints already tell you what's wrong the query because of the actions mysql will take in gathering data from each table. The key is to understand what the temp tables will look like while the query is being processed and where the data will reside (memory or disk).

What you need to do is refactor the query to fool the MySQL Query Optimizer. Force the query to produce smaller temp tables. In most cases, config changes in my.cnf should make a dramamtic difference. In other cases, such as this one, refactoring the query may be sufficient.

Here is my proposed change to your query that should work faster:

SELECT
    ua.*,
    u.twitter_username,
    u.email
FROM
    (SELECT * FROM `user_actions`
    ORDER BY timestamp DESC LIMIT 30) ua
    LEFT JOIN
    (SELECT id,twitter_username,email FROM `users`) u
    ON (ua.user_id=u.id)
;

Here are reasons for refactoring the query:

REASON #1

If you look at inline table ua, I retrieve only 30 rows using LIMIT. This will happen no matter how big the user_actions table gets. It is already ordered because the ORDER BY timestamp DESC happens before the LIMIT.

REASON #2

If you look inline table u, it has id,twitter_username,email. The id is needed to implement the join.

REASON #3

I use LEFT JOIN instead of INNER JOIN for two(2) reasons:

1. Preserve the order of the query based on ua
2. Display all user actions in case the user_id in the ua no longer exists in the users tables.

Doing these things will force the temp tables to be smaller. Nevertheless, you will still need implement bulletpoint #3 from @DTest's answer to preempt having temp tables land on disk.

It might be worth posting the table definition from your other question for clarity.

The composite index is doing a few things for you:

• As you know, enforcing uniqueness on (sensor_id, timestamp); I'm unsure whether this is an important data integrity constraint.
• Allowing queries that filter on both columns to look up matching rows by using a single index. MySQL can answer some queries (equality conditions on multiple columns are the ones I know about) by merging two indexes, but this tends to be significantly slower compared to using a single composite index.
• The index can also be used to search for values in a left-based subset of the composite index, but not a right-based subset. So in this case it could help a query that filters on sensor_id values or sensor_id and timestamp values, but not timestamp values alone.

There are a number of caveats to this, so it's good idea to look at the EXPLAIN output for your queries and verify what indexes they're using. Keep also in mind that indexes can support the read part of UPDATE and DELETE queries, as well as JOINs, GROUP BY, ORDER BY, and other operations I'm neglecting.

An example of a scenario where the composite index is unnecessary would be if you don't care about the uniqueness constraint and all your queries filter on timestamp or sensor_id, but not both.

The single-column index on sensor_id is actually redundant since the composite index on (sensor_id, timestamp) can be used by the same queries, but still you might find that some queries perform faster when doing scans on the single-column index compared to using a composite index with a wider key. The difference might not be enough to matter, though, and some testing will probably be required to find out.

In addition to looking at the EXPLAIN output for your queries, tools such as pt-index-usage from the Percona toolkit or the table INFORMATION_SCHEMA.INDEX_STATISTICS if you're running Percona Server or MariaDB can help you assess what indexes are actually being used.

References:

The Optimization That (Often) Isn’t: Index Merge Intersection

Practical MySQL indexing guidelines