List:General Discussion« Previous MessageNext Message »
From:Michael Widenius Date:August 9 1999 7:50am
Subject:Use of Indexes
View as plain text  
>>>>> "Quentin" == Quentin Bennett <quentin.bennett@stripped>
> writes:

Quentin> Hi,
Quentin> I might be showing my DB inexperience here, but anyway. What I want to be
Quentin> able to do is to retrieve a list of customers, primary index Code, ordered
Quentin> by name. My C-ISAM experience says "create an index on name, and use that".
Quentin> I can create an index on the name column, but cannot force the use of it.
Quentin> MySQL, it seems, knows better..

mysql> show index from customer;
Quentin> +----------+------------+----------+--------------+-------------+-----------
Quentin> +-------------+----------+
Quentin> | Table    | Non_unique | Key_name | Seq_in_index | Column_name | Collation
Quentin> | Cardinality | Sub_part |
Quentin> +----------+------------+----------+--------------+-------------+-----------
Quentin> +-------------+----------+
Quentin> | customer |          0 | Code     |            1 | Code        | A
Quentin> |       50576 |     NULL |
Quentin> | customer |          1 | name     |            1 | name        | A
Quentin> |        NULL |       10 |
Quentin> +----------+------------+----------+--------------+-------------+-----------
Quentin> +-------------+----------+
Quentin> 2 rows in set (0.01 sec)
 
mysql> explain select name from customer where name < 'ZZZZZZZZZ';
Quentin> +----------+------+---------------+------+---------+------+-------+---------
Quentin> ---+
Quentin> | table    | type | possible_keys | key  | key_len | ref  | rows  | Extra
Quentin> |
Quentin> +----------+------+---------------+------+---------+------+-------+---------
Quentin> ---+
Quentin> | customer | ALL  | name          | NULL |    NULL | NULL | 50576 | where
Quentin> used |
Quentin> +----------+------+---------------+------+---------+------+-------+---------
Quentin> ---+
Quentin> 1 row in set (0.01 sec)
 
mysql> explain select name from customer;
Quentin> +----------+------+---------------+------+---------+------+-------+-------+
Quentin> | table    | type | possible_keys | key  | key_len | ref  | rows  | Extra |
Quentin> +----------+------+---------------+------+---------+------+-------+-------+
Quentin> | customer | ALL  | NULL          | NULL |    NULL | NULL | 50576 |       |
Quentin> +----------+------+---------------+------+---------+------+-------+-------+
Quentin> 1 row in set (0.01 sec)
 
mysql> explain select name from customer order by name;
Quentin> +----------+------+---------------+------+---------+------+-------+-------+
Quentin> | table    | type | possible_keys | key  | key_len | ref  | rows  | Extra |
Quentin> +----------+------+---------------+------+---------+------+-------+-------+
Quentin> | customer | ALL  | NULL          | NULL |    NULL | NULL | 50576 |       |
Quentin> +----------+------+---------------+------+---------+------+-------+-------+
Quentin> 1 row in set (0.01 sec)

Quentin> The first query quickly shows the names in the desired order, A-Z, but the
Quentin> second shows them in code order. The third, of course, works, but takes 17.6
Quentin> seconds to return the first 10 rows.

Quentin> In the absence of SELECT USING, what is the best way of ensuring that the
Quentin> appropriate index is used?

Hi!

The first query will not show the data in sorted order.

MySQL doesn't use indexes if a query would match more than 1/3 of the
database. In this case it's almost always better to use table scanning 
instead of using the index tree because table scanning needs fewer
disk seeks. (Normally it's disk seeks that takes time)

17 seconds to sort 50576 rows seems however a little too much, but
without more information about this it's hard to say what is going on.

To help you, I would need the output from 'mysqlbug' + some
information of how you have started MySQL.

Regards,
Monty
Thread
Use of IndexesMichael Widenius9 Aug
RE: Use of IndexesQuentin Bennett9 Aug
  • Re: Use of IndexesBenjamin Pflugmann10 Aug