List:General Discussion« Previous MessageNext Message »
From:Dan Nelson Date:December 11 2004 5:26am
Subject:Re: Why is this simple query so slow?
View as plain text  
In the last episode (Dec 10), Aaron said:
> The query below takes around 8 seconds, and returns 3253 rows.
>  
> Query:SELECT ID FROM Offers_To_Buy FORCE INDEX(scdd) WHERE subcatID = 72;
> 3253 rows in set (8.00 sec)
>  
> Explain says: 

Bad word-wrapping fixed:

> mysql> EXPLAIN SELECT ID FROM Offers_To_Buy FORCE INDEX(scdd) WHERE subcatID = 72
> ;
>
> +----+-------------+---------------+------+---------------+------+---------+-------+------+-------------+
> | id | select_type | table         | type | possible_keys | key  | key_len | ref   |
> rows | Extra       |
>
> +----+-------------+---------------+------+---------------+------+---------+-------+------+-------------+
> |  1 | SIMPLE      | Offers_To_Buy | ref  | scdd          | scdd |       4 | const |
> 2988 | Using where |
>
> +----+-------------+---------------+------+---------------+------+---------+-------+------+-------------+
> 1 row in set (0.02 sec)
>  
> mysql> SHOW INDEXES FROM Offers_To_Buy ; 
>
> +---------------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
> | Table         | Non_unique | Key_name    | Seq_in_index |  Column_name| Collation |
> Cardinality | Sub_part | Packed | Null | Index_type | Comment |
>
> +---------------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
> | Offers_To_Buy |          1 | subcategory |            1 |    subcatID | A         |
>        NULL |     NULL | NULL   |      | BTREE      |         |
> | Offers_To_Buy |          1 | scdd        |            1 |    subcatID | A         |
>        NULL |     NULL | NULL   |      | BTREE      |         |
> | Offers_To_Buy |          1 | scdd        |            2 |  deletedate | A         |
>        NULL |     NULL | NULL   |      | BTREE      |         |
>
> +---------------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
> 11 rows in set (0.00 sec)

As Donny said, your FORCE INDEX(scdd) is pessimizing you.  But even
moving to the subcategory key won't help you much, since you're still
going to be doing 3253 random reads into your table to fetch 'id'.  Try
creating an index on (subcatID,ID).  This will let mysql retrieve all
the data it needs from an index range scan.

-- 
	Dan Nelson
	dnelson@stripped
Thread
Why is this simple query so slow?Aaron11 Dec
  • Re: Why is this simple query so slow?Victor Pendleton11 Dec
  • RE: Why is this simple query so slow?Donny Simonton11 Dec
  • Re: Why is this simple query so slow?Dan Nelson11 Dec