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?
将datetime_field列索引有帮助吗？即，索引仅在使用相等（或不等式）测试时有用，或者在进行有序比较时是否有用？
(Suggestions for better executing this query, without recreating the table, would also be ok!)
（更好地执行此查询的建议，无需重新创建表，也可以！）
5 个解决方案
#1
11
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.
也许。通常，如果存在这样的索引，如果查询上没有“更好”的索引，它将对该索引使用范围扫描。但是，如果优化器确定范围最终会过大（即包括超过1/3的行），则可能根本不会使用索引，因为表扫描很可能是更快。
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
使用EXPLAIN（在SELECT;您不能解析删除）来确定在特定情况下的决定。这可能取决于
- 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".
- WHERE子句中指定了什么。如果有另一个“看起来更好”的索引，它将不会使用一个索引的范围扫描。
#2
4
From MySQL Reference manual:
来自MySQL参考手册：
A B-tree index can be used for column comparisons in expressions that use the =, >, >=, <, <=, or BETWEEN operators.
B树索引可用于使用=，>，> =，<，<=或BETWEEN运算符的表达式中的列比较。
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.
对于大量行，通过树索引查找行比通过表扫描查找行要快得多。但正如其他答案所指出的那样，使用EXPLAIN来找出MySQL的决定。
#3
2
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.
日期时间字段上的索引肯定有助于基于日期范围的搜索。我们一直在数据库中使用它们，并且在没有索引的情况下查询会非常缓慢。
#4
1
It does, check with a DESCRIBE SELECT FROM table ...
它确实，用DESCRIBE SELECT FROM表检查......
#5
0
Except you don't have this bug in mysql http://bugs.mysql.com/bug.php?id=58190
除了你在mysql中没有这个bug http://bugs.mysql.com/bug.php?id=58190
#1
11
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.
也许。通常，如果存在这样的索引，如果查询上没有“更好”的索引，它将对该索引使用范围扫描。但是，如果优化器确定范围最终会过大（即包括超过1/3的行），则可能根本不会使用索引，因为表扫描很可能是更快。
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
使用EXPLAIN（在SELECT;您不能解析删除）来确定在特定情况下的决定。这可能取决于
- 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".
- WHERE子句中指定了什么。如果有另一个“看起来更好”的索引，它将不会使用一个索引的范围扫描。
#2
4
From MySQL Reference manual:
来自MySQL参考手册：
A B-tree index can be used for column comparisons in expressions that use the =, >, >=, <, <=, or BETWEEN operators.
B树索引可用于使用=，>，> =，<，<=或BETWEEN运算符的表达式中的列比较。
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.
对于大量行，通过树索引查找行比通过表扫描查找行要快得多。但正如其他答案所指出的那样，使用EXPLAIN来找出MySQL的决定。
#3
2
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.
日期时间字段上的索引肯定有助于基于日期范围的搜索。我们一直在数据库中使用它们，并且在没有索引的情况下查询会非常缓慢。
#4
1
It does, check with a DESCRIBE SELECT FROM table ...
它确实，用DESCRIBE SELECT FROM表检查......
#5
0
Except you don't have this bug in mysql http://bugs.mysql.com/bug.php?id=58190
除了你在mysql中没有这个bug http://bugs.mysql.com/bug.php?id=58190