List:General Discussion« Previous MessageNext Message »
From:Michael Widenius Date:November 9 1999 10:52pm
Subject:Re: Optimizer behavior with date ranges optimal?
View as plain text  
>>>>> "Maurice" == Maurice Aubrey <maurice@stripped> writes:

Maurice> On Tue, Nov 02, 1999 at 04:40:30PM +0200, sinisa@stripped wrote:
>> Maurice Aubrey writes:

Maurice> <snip>

>> > mysql> EXPLAIN SELECT DISTINCT registration FROM logins WHERE login_date
> >= DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY) AND login_date < CURRENT_DATE();
>> >
> +--------+------+---------------+------+---------+------+--------+------------+
>> > | table  | type | possible_keys | key  | key_len | ref  | rows   | Extra    
>  |
>> >
> +--------+------+---------------+------+---------+------+--------+------------+
>> > | logins | ALL  | login_date    | NULL |    NULL | NULL | 120000 | where
> used |
>> >
> +--------+------+---------------+------+---------+------+--------+------------+
>> > 1 row in set (0.00 sec)                                      
>> > 
>> > So, even though we have an index on the login_date column, the optimizer
>> > chooses not to use it, and instead scans 120,000 rows to find the unique
>> > registration numbers?  With the value distribution above, is that still
>> > considered optimal? 

Maurice> <snip>

>> Lend your query some help and use BETWEEN for specifiying a date range 
>> !!!

Maurice> That doesn't seem to improve things...

mysql> EXPLAIN SELECT DISTINCT registration FROM logins WHERE login_date BETWEEN
Maurice> '1999-10-30' AND '1999-11-05';
Maurice>
> +--------+------+---------------+------+---------+------+--------+------------+
Maurice> | table  | type | possible_keys | key  | key_len | ref  | rows   | Extra     
> |
Maurice>
> +--------+------+---------------+------+---------+------+--------+------------+
Maurice> | logins | ALL  | login_date    | NULL |    NULL | NULL | 120000 | where used
> |
Maurice>
> +--------+------+---------------+------+---------+------+--------+------------+
Maurice> 1 row in set (0.00 sec)       

mysql> SELECT DISTINCT registration FROM logins WHERE login_date BETWEEN '1999-10-30'
> AND '1999-11-05';   
Maurice> <...data snipped>
Maurice> 20000 rows in set (0.76 sec)

>> Sorry, but if you are licenced user, you are not liable for a support
>> through mysql-support@stripped.

Maurice> We should have both a license and extended email support (#351).
Maurice> Sorry I didn't include it in the last message.

Hi!

BETWEEN should not change things.

Note that normally it takes much longer to read things through keys
instead of scanning a table as key-reads will involve a lot of seeks.

MySQL calcuates how long it takes to scan a table as follows:

read_time=data_file_length/IO_SIZE + records / TIME_FOR_COMPARE
 
(IO_SIZE = 1024 and TIME_FOR_COMPARE is 10 in MySQL 3.22 and 5 in
MySQL 3.23 (this will force MySQL 3.23 to use indexes more than 3.22)

In your case, MySQL should be able to notice that if you use the
login_date index, MySQL will have to read about 2000 rows
(read_time = 2000).

It's hard to say which version would be faster in your case.  If the
data file length is quite small, then it's probably faster to scan the 
table than doing key lookups (at least if you have a lot of disk IO
going on)

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