MySQL not using index on DATE when used with '<' or '>' operators?

Question

I'm using explain to test these queries. The col type is DATE

this uses index:

explain SELECT events.* FROM events WHERE events.date = '2010-06-11'

this doesnt

explain SELECT events.* FROM events WHERE events.date >= '2010-06-11'

index as follows (phpmyadmin)

Action Keyname Type Unique Packed Field Cardinality Collation Null Comment Edit Drop PRIMARY BTREE Yes No event_id 18 A Edit Drop date BTREE No No date 0 A

i notice cardinality is 0, though there are some rows with the same date..

Cardinality 0 is a bit odd. Try running ANALYZE TABLE events and then try again? — Hammerite
– Hammerite, Commented Jun 18, 2010 at 11:13
and does the output of EXPLAIN still state that the index has cardinality 0? — Hammerite
– Hammerite, Commented Jun 18, 2010 at 11:23
If you actually run the query, does it give the output you would expect? — Hammerite
– Hammerite, Commented Jun 18, 2010 at 11:29

Wrikken · Accepted Answer · 2010-06-18 12:21:30Z

If MySQL doesn't use the index, it has seen your query, and estimated that a table scan would probably be faster then using the index (in terms of IO / disk operations required probably). You can use a FORCE INDEX and check whether this query will actually be faster using the index or not.

SELECT events.* FROM events FORCE INDEX (date) WHERE events.date >= '2010-06-11';

yes i think you're right, as its a test table with only 20rows (will be tens of thousands). how do i implement FORCE (to test)? this doesnt work: explain SELECT events.* FROM events WHERE events.date >= '2010-06-11' FORCE INDEX(date)

Collectives™ on Stack Overflow

MySQL not using index on DATE when used with '<' or '>' operators?

1 Answer 1

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Linked

Related