MySQL simple but slow query (wrong indexes?)

Question

i'm working on a simple query that runs in about 1.2 seconds in a myisam table populated by 126,000 records:

SELECT * FROM my_table
WHERE primary_key != 5 AND
(
    col1 = 528 OR (col2 = 265 AND col3 = 1)
)
ORDER BY primary_key DESC

I have already created single indexes for each field used in the where clause, but only primary_key (autoincrement field of my_table) is used as key while col1 and col2 are just ignored and the query becomes much slower. How should I create the indexes (maybe multiple-indexs) or edit the query?

The indexes must be in the same direction of your query (DESC). — Alfabravo
– Alfabravo, Commented Feb 10, 2012 at 17:10

Marcus Adams · Accepted Answer · 2012-02-10 17:48:48Z

You will get optimal performance if you have the following multi-column "covering" indexes:

(primary_key, col1)
(primary_key, col2, col3)

And issue the following query:

(SELECT * FROM my_table
WHERE primary_key != 5 AND
  col1 = 528)
UNION
(SELECT * FROM my_table
WHERE primary_key != 5 AND
 col2 = 265 AND col3 = 1)
ORDER BY primary_key DESC

You may get variable, better, performance by changing the order of the fields in the indexes, based on cardinality.

In your original query, no index could be used for the entire selection in the WHERE clause, which caused partial table scanning.

In the above query, the first subquery is able to utilize the first index completely, avoiding any table scanning, and the second subquery uses the second index.

Unfortunately, MySQL won't be able to utilize an index to sort the records on the full result set, and will probably use filesort to order them. So, if you don't need the records ordered by primary_key, remove the outer ORDER clause for better performance, though if the result set is small, it shouldn't be an issue either way.

Ed Heal · Accepted Answer · 2012-02-10 17:20:34Z

0

Use EXPLAIN to find out what is going on. This will identify what indexes are being used and hence enable you to tune the query.

answered Feb 10, 2012 at 17:20

Ed Heal

60.3k18 gold badges91 silver badges137 bronze badges

Comments

Brenton Alker · Accepted Answer · 2012-02-10 17:24:17Z

0

Unfortunately, there is almost no way to predict which indexes will work better than others without a thorough understanding of MySQL internals. However, for each query (or, at least sub-query/join) MySQL will only use 1 index, you have stated that the primary key is being used, so I assume you have looked at the EXPLAIN output.

You will likely want to try multi-column indexes on either all of (primary_key, col1, col2, col3) or a subset of these, in different orders to find the best result. The best index will depend on the primarily on the cardinality of the columns and thus, may even change over time depending on the data in the table.

answered Feb 10, 2012 at 17:24

Brenton Alker

9,1223 gold badges38 silver badges37 bronze badges

Collectives™ on Stack Overflow

MySQL simple but slow query (wrong indexes?)

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related