CODEWITHSUNDEEP
mysqlindexing
randombits
randombits

Reputation: 48450

MySQL index misses

I have a query that looks like the following:

select count(*) from `foo` where expires_at < now()”

since expires_at is indexed, the query hits the index no problem. however the following query:

select count(*) from `foo` where expires_at < now() and some_id != 5

the index never gets hit.

both expires_at and some_id are indexed.

is my index not properly created?

Upvotes: 3

Views: 168

Answers (3)

Iqster
Iqster

Reputation: 1

1) It seems you have two single-column indices. You can try to create a multi-column index.

For a detailed explanation why this is different than multiple single column indices, see the following: http://www.mysqlfaqs.net/mysql-faqs/Indexes/When-does-multi-column-index-come-into-use-in-MySQL

2) Do you have a B-tree index on the expires_at column? Since you are doing a range query (<), that might give better performance.

http://dev.mysql.com/doc/refman/5.0/en/mysql-indexes.html

Best of luck!

Upvotes: 0

Hammerite
Hammerite

Reputation: 22340

Probably what's happening is that for the first query, the index can be used to count the rows satisfying the WHERE clause. In other words, the query would result in a table scan, but happily all the columns involved in the WHERE condition are in an index, so the index is scanned instead.

In the second query though, there's no single index that contains all the columns in the WHERE clause. So MySQL resorts to a full table scan. In the case of the first query, it was using your index, but not to find the rows to check - in the special case of a COUNT() query, it could use the index to count rows. It was doing the equivalent of a table scan, but on the index instead of the table.

Upvotes: 0

Quassnoi
Quassnoi

Reputation: 425371

This query:

SELECT  COUNT(*)
FROM    foo
WHERE   expires_at < NOW()

can be satisfied by the index only, without referring to the table itself. You may see it from the using index in the plan.

This query:

SELECT  COUNT(*)
FROM    foo
WHERE   expires_at < NOW()
        AND some_id <> 5

needs to look into the table to find the value of some_id.

Since the table lookup is quite an expensive thing, it is more efficient to use the table scan and filter the records.

If you had a composite index on expires_at, some_id, the query would probably use the index both for ranging on expires_at and filtering on some_id.

SQL Server even offers a feature known as included fields for this. This command

CREATE INDEX ix_foo_expires__someid ON foo (expires_at) INCLUDE (some_id)

would create an index on expires_at which would additionally store some_id in the leaf entires (without overhead of sorting).

MySQL, unfortunately, does not support it.

Upvotes: 1

Related Questions