How do I create one MySQL index for 2 SQL queries?

Question

SELECT * FROM messages_messages WHERE (from_user_id=? AND to_user_id=?) OR (from_user_id=? AND to_user_id=?) ORDER BY created_at DESC

I have another query, which is this:

SELECT COUNT(*) FROM messages_messages WHERE from_user_id=? AND to_user_id=? AND read_at IS NULL

I want to index both of these queries, but I don't want to create 2 separate indexes.

Right now, I'm using 2 indexes:

[from_user_id, to_user_id, created_at]
[from_user_id, to_user_id, read_at]

I was wondering if I could do this with one index instead of 2? These are the only 2 queries I have for this table.

Are you that space limited that you cant do it with two? What happens when you create the index [from_user_id, to_user_id, created_at, read_at]? — crthompson
– crthompson, Commented Oct 15, 2014 at 22:04

John Bollinger · Accepted Answer · 2014-10-15 22:18:17Z

1

The docs explain fairly completely how MySQL uses indices. In particular, its optimizer can use any left prefix of a multi-column index. Therefore, you could drop either of your two existing indices, and the other would be eligible for use in both queries, though it would be more selective / useful for one than for the other.

In principle, it could be more beneficial to keep your first index, provided that the created_at column was indexed in descending order. In practice, MySQL allows you to specify index column order, but in fact implements only ascending order. Therefore, having created_at in your index probably doesn't help very much.

edited Oct 15, 2014 at 22:18

answered Oct 15, 2014 at 22:10

John Bollinger

191k11 gold badges103 silver badges206 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

TIMEX Over a year ago

How do I index it in descending order? It's possible to specify?

John Bollinger Over a year ago

In a DBMS that supported it, you would add the DESC keyword after the appropriate column name in your index definition. MySQL is not such a DBMS. As I explained in my answer, MySQL accepts that syntax but does not honor it. With MySQL, therefore, dropping the index with created_at probably would have little impact on your query performance.

Bill Karwin · Accepted Answer · 2014-10-15 22:14:40Z

No, you need both indexes for these two queries if you want to optimize fully.

Once you reach the column used for either sorting or range comparison (IS [NOT] NULL counts as a range predicate for this purpose), you don't get any benefit from putting more columns in the index. In other words, your index can have:

Some columns that are used in equality predicates
One column that is used either in a range predicate, or to avoid a filesort -- but not both.
Extra columns used in neither searching nor sorting, but only for the sake of a covering index.

So you cannot make a four-column index that serves both queries.

The only way you can reduce this to one index, as @JohnBollinger says, is to make an index that optimizes for one query, and uses a subset of the index for the second query. But that won't work as well.

Collectives™ on Stack Overflow

How do I create one MySQL index for 2 SQL queries?

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related