List:General Discussion« Previous MessageNext Message »
From:Michael Widenius Date:November 9 1999 10:59pm
Subject:Re: Optimizer behavior with date ranges optimal?
View as plain text  
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
Thread
Optimizer behavior with date ranges optimal?maurice1 Nov
  • Re: Optimizer behavior with date ranges optimal?sinisa1 Nov
    • Re: Optimizer behavior with date ranges optimal?Maurice Aubrey2 Nov
      • Re: Optimizer behavior with date ranges optimal?sinisa2 Nov
        • Re: Optimizer behavior with date ranges optimal?Maurice Aubrey2 Nov
          • Re: Optimizer behavior with date ranges optimal?sinisa3 Nov
            • Re: Optimizer behavior with date ranges optimal?Maurice Aubrey4 Nov
              • Re: Optimizer behavior with date ranges optimal?sinisa4 Nov
                • Re: Optimizer behavior with date ranges optimal?Maurice Aubrey4 Nov
                  • Re: Optimizer behavior with date ranges optimal?sinisa5 Nov
                    • Re: Optimizer behavior with date ranges optimal?Maurice Aubrey6 Nov
                      • Re: Optimizer behavior with date ranges optimal?sinisa6 Nov
          • Re: Optimizer behavior with date ranges optimal?Michael Widenius10 Nov
  • Re: Optimizer behavior with date ranges optimal?Steve Ruby2 Nov
    • Re: Optimizer behavior with date ranges optimal?Maurice Aubrey3 Nov
      • RE: Optimizer behavior with date ranges optimal?John Correa3 Nov
  • Re: Optimizer behavior with date ranges optimal?Steve Ruby2 Nov
  • Re: Optimizer behavior with date ranges optimal?Steve Ruby3 Nov
    • Re: Optimizer behavior with date ranges optimal?Michael Widenius10 Nov
  • Optimizer behavior with date ranges optimal?Michael Widenius10 Nov
  • Re: Optimizer behavior with date ranges optimal?Steve Ruby10 Nov