List:General Discussion« Previous MessageNext Message »
From:Michael Widenius Date:February 8 2000 11:42am
Subject:Index in MySQL
View as plain text  
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

Thread
Index in MySQLAndrew Sitnikov3 Feb
  • Re: Index in MySQLKristo3 Feb
    • Re: Index in MySQLMatthias Urlichs3 Feb
  • Index in MySQLMichael Widenius8 Feb