Steve,
Wednesday, March 20, 2002, 6:31:09 PM, you wrote:
SG> Could someone explain to me why an index in a select I am doing is only
SG> sometimes used.
SG> The select I do is a complex one with multiple tables, and when it uses the
SG> index it takes 1.5 seconds, but when it dosnt it takes 1 min and 25-50
SG> seconds.
SG> I have simplified it down and founed the place the problem is....see
SG> explains bellow:
SG> mysql> explain select * from mailstat where domain_id in(1,3,4,7,50,20,11);
SG> +----------+-------+---------------+------+---------+------+--------+-------
SG> -----+
SG> | table | type | possible_keys | key | key_len | ref | rows | Extra
SG> |
SG> +----------+-------+---------------+------+---------+------+--------+-------
SG> -----+
SG> | mailstat | range | ind4 | ind4 | 4 | NULL | 239862 | where
SG> used |
SG> +----------+-------+---------------+------+---------+------+--------+-------
SG> -----+
SG> 1 row in set (0.00 sec)
SG> *notice ind4 is used
SG> mysql> explain select * from mailstat where domain_id in(1,2);
SG> +----------+------+---------------+------+---------+------+---------+-------
SG> -----+
SG> | table | type | possible_keys | key | key_len | ref | rows | Extra
SG> |
SG> +----------+------+---------------+------+---------+------+---------+-------
SG> -----+
SG> | mailstat | ALL | ind4 | NULL | NULL | NULL | 2955648 | where
SG> used |
SG> +----------+------+---------------+------+---------+------+---------+-------
SG> -----+
SG> 1 row in set (0.00 sec)
SG> *notice ind4 is NOT used
SG> mysql> explain select * from mailstat where domain_id in(1);
SG> +----------+-------+---------------+------+---------+------+--------+-------
SG> -----+
SG> | table | type | possible_keys | key | key_len | ref | rows | Extra
SG> |
SG> +----------+-------+---------------+------+---------+------+--------+-------
SG> -----+
SG> | mailstat | range | ind4 | ind4 | 4 | NULL | 174922 | where
SG> used |
SG> +----------+-------+---------------+------+---------+------+--------+-------
SG> -----+
SG> 1 row in set (0.00 sec)
SG> *notice ind4 is used
SG> mysql> explain select * from mailstat where domain_id in(2);
SG> +----------+------+---------------+------+---------+------+---------+-------
SG> -----+
SG> | table | type | possible_keys | key | key_len | ref | rows | Extra
SG> |
SG> +----------+------+---------------+------+---------+------+---------+-------
SG> -----+
SG> | mailstat | ALL | ind4 | NULL | NULL | NULL | 2955666 | where
SG> used |
SG> +----------+------+---------------+------+---------+------+---------+-------
SG> -----+
SG> 1 row in set (0.00 sec)
SG> *notice ind4 is NOT used
SG> Basicaly, when I use '2' in the in() statement (and one or two other values
SG> from hundreds that do work), the index is not used.
SG> Initialy I thought that maybe the index was corupted, so I built a copy of
SG> the table, and inserted a handfull of rows, but the I got the same results,
SG> except for my new table 1 and 3 didnt work, but 2 did!.
SG> Out of interest I tried other indexes and found results like:
SG> mysql> explain select * from mailstat where id<558693;
SG> +----------+-------+---------------+---------+---------+------+--------+----
SG> --------+
SG> | table | type | possible_keys | key | key_len | ref | rows |
SG> Extra |
SG> +----------+-------+---------------+---------+---------+------+--------+----
SG> --------+
SG> | mailstat | range | PRIMARY | PRIMARY | 4 | NULL | 511375 |
SG> where used |
SG> +----------+-------+---------------+---------+---------+------+--------+----
SG> --------+
SG> 1 row in set (0.00 sec)
SG> *primary key is used
SG> mysql> explain select * from mailstat where id<558694;
SG> +----------+------+---------------+------+---------+------+---------+-------
SG> -----+
SG> | table | type | possible_keys | key | key_len | ref | rows | Extra
SG> |
SG> +----------+------+---------------+------+---------+------+---------+-------
SG> -----+
SG> | mailstat | ALL | PRIMARY | NULL | NULL | NULL | 2956363 | where
SG> used |
SG> +----------+------+---------------+------+---------+------+---------+-------
SG> -----+
SG> 1 row in set (0.00 sec)
SG> *primary key is NOT used
SG> If anyone could shed some light on this I would b most greatful :)
It will be useful for you to read how MySQL uses indexes. In some
cases MySQL doesn't use indexes. If the result of query is more
than 30% rows in a table, MySQL won't use indexes. It works much
faster without index usage.
You can read about it at:
http://www.mysql.com/doc/M/y/MySQL_indexes.html
SG> Cheers, Steve
--
For technical support contracts, goto https://order.mysql.com/
This email is sponsored by Ensita.net http://www.ensita.net/
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Victoria Reznichenko
/ /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED]
/_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net
<___/ www.mysql.com
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
