CODEWITHSUNDEEP
mysqlindexing
Fabbro
Fabbro

Reputation: 21

MySQL simple but slow query (wrong indexes?)

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?

Upvotes: 2

Views: 213

Answers (3)

Marcus Adams
Marcus Adams

Reputation: 53830

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.

Upvotes: 4

Brenton Alker
Brenton Alker

Reputation: 9072

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.

Upvotes: 0

Ed Heal
Ed Heal

Reputation: 59997

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.

Upvotes: 0

Related Questions