Hi!
<cut>
mysql> explain select * from _data where datadatetime between
Steve> '1999-01-01' and '1999-04-01';
Steve> +-------+------+---------------+------+---------+------+---------+------------+
Steve> | table | type | possible_keys | key | key_len | ref | rows |
Steve> Extra |
Steve> +-------+------+---------------+------+---------+------+---------+------------+
Steve> | _data | ALL | datadatetime | NULL | NULL | NULL | 1821375 | where
Steve> used |
Steve> +-------+------+---------------+------+---------+------+---------+------------+
Steve> 1 row in set (0.07 sec)
Steve> Where datadatetime is a datetime type column and is obviously indexed
Steve> but the index is not being used
Steve> for between or <=,>= range type queries.
As you can see from 'possible_keys', MySQL did try to use datadatetime
as a key, but found out that doing a table scan would be faster.
Steve> However when I actually moved this same table over to 3.23.5a I find
Steve> that the same query is also not
Steve> using the index. Any help here, I see no reason why it should not use an
Steve> index, if it will for
Steve> text fields and number fields.
How many rows does satisfy the above query? Could you try with a
smaller query range that satisfies even fewer rows?
datetime and BETWEEN, >= and <= should use keys..
Regards,
Monty
