CODEWITHSUNDEEP
mysqloptimizationindexing
Tony Meyer
Tony Meyer

Reputation: 10157

Does an index < or > MySQL queries?

If I have a query like, 

DELETE FROM table WHERE datetime_field < '2008-01-01 00:00:00'

does having the datetime_field column indexed help? i.e. is the index only useful when using equality (or inequality) testing, or is it useful when doing an ordered comparison as well?

(Suggestions for better executing this query, without recreating the table, would also be ok!)

Upvotes: 10

Views: 2601

Answers (5)

Razvan Tudorica
Razvan Tudorica

Reputation: 475

Except you don't have this bug in mysql http://bugs.mysql.com/bug.php?id=58190

Upvotes: 0

Bruno De Fraine
Bruno De Fraine

Reputation: 47286

From MySQL Reference manual:

A B-tree index can be used for column comparisons in expressions that use the =, >, >=, <, <=, or BETWEEN operators.

For a large number of rows, it can be much faster to lookup the rows through a tree index than through a table scan. But as the other answers point out, use EXPLAIN to find out MySQL's decision.

Upvotes: 4

MarkR
MarkR

Reputation: 63538

Maybe. In general, if there is such an index, it will use a range scan on that index if there is no "better" index on the query. However, if the optimiser decides that the range would end up being too big (i.e. include more than, say 1/3 of the rows), it probably won't use the index at all, as a table scan would be likely to be faster.

Use EXPLAIN (on a SELECT; you can't EXPLAIN a delete) to determine its decision in a specific case. This is likely to depend upon

  • How many rows there are in the table
  • What the range is that you're specifying
  • What else is specified in the WHERE clause. It won't use a range scan of one index if there is another index which "looks better".

Upvotes: 11

Jarod Elliott
Jarod Elliott

Reputation: 15670

An index on the datetime field will definitely help with date range based searches. We use them all the time in our databases and the queries are rediculously slow without the indexes.

Upvotes: 2

Matthias Winkelmann
Matthias Winkelmann

Reputation: 16384

It does, check with a DESCRIBE SELECT FROM table ...

Upvotes: 1

Related Questions