Geek asks and answers

MySQL Error Index

I have a query that looks like this:

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

Since expires_at is indexed, the query falls into the index without any problems. however, the following query:

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

the index never hits.

both expires_at and some_id are indexed.

Is my index created incorrectly?

+3
source share
3 answers

This request:

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

can be satisfied only with the index, not referring to the table itself. You can see it from using indexthe plan.

This request:

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

you need to look in the table to find the value some_id.

Since table searches are quite expensive, it is more efficient to use table scans and filter records.

expires_at, some_id, , , expires_at, some_id.

SQL Server , included fields.

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

expires_at, some_id ( ).

MySQL, , .

+1

, , , WHERE. , , , , , WHERE, , .

, , , WHERE. MySQL . , COUNT(), . , .

0

More articles:


All Articles