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
[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
