Hi!
>>>>> "Andrew" == Andrew Sitnikov <sitnikov@stripped> writes:
Andrew> Mysql server version: 3.23.9-alpha-log
Andrew> I have table tblAccountActivity
Andrew> explain tblAccountActivity;
<cut>
Andrew> EXPLAIN SELECT tblAccountActivity.eDate,tblAccountActivity.Taxed FROM
Andrew> tblAccountActivity
Andrew> WHERE tblAccountActivity.eDate BETWEEN '1999-01-01' AND '1999-12-01'
Andrew>
> +--------------------+------+---------------+------+---------+------+-------+------------+
Andrew> | table | type | possible_keys | key | key_len | ref | rows
Andrew> | Extra |
Andrew>
> +--------------------+------+---------------+------+---------+------+-------+------------+
Andrew> | tblAccountActivity | ALL | eDate | NULL | NULL | NULL |
Andrew> 76060 | where used |
Andrew>
> +--------------------+------+---------------+------+---------+------+-------+------------+
Andrew> 1 row in set (0.00 sec)
Andrew> 2.
Andrew> EXPLAIN SELECT tblAccountActivity.eDate FROM tblAccountActivity
Andrew> WHERE tblAccountActivity.eDate BETWEEN '1999-01-01' AND '1999-12-01'
Andrew>
> +--------------------+-------+---------------+-------+---------+------+-------+-------------------------+
Andrew> | table | type | possible_keys | key | key_len | ref |
Andrew> rows | Extra |
Andrew>
> +--------------------+-------+---------------+-------+---------+------+-------+-------------------------+
Andrew> | tblAccountActivity | range | eDate | eDate | 3 | NULL |
Andrew> 35501 | where used; Using index |
Andrew>
> +--------------------+-------+---------------+-------+---------+------+-------+-------------------------+
Andrew> 1 row in set (0.00 sec)
Andrew> Why the 1 query don't use index(eDate)?
Because it's probably faster for MySQL to scan the table than use an
index on it, as one would need to do 76060 seeks to find the rows. In
the second case mysqld only needs to access the index tree (That
what's 'using index' means) and not the rows to get the result set
and because of this it's better to use the index.
Regards,
Monty
