Have you tried optimizing or run an analyze table command on this table?
Aaron wrote:
>Hi all ,
>
>I have a relatively simple query that is taking longer than I think it
>should. Can anyone possibly give me some idea why this might be or any
>potential bottleneck areas I might want to check out?
>
>thanks!
>
>Here is some information.
>
>The query below takes around 8 seconds, and returns 3253 rows.
>
>Mysql Version: 4.1.7-standard-log
>Operating System: Linux 2.4.20-8smp
>Query:SELECT ID FROM Offers_To_Buy FORCE INDEX(scdd) WHERE subcatID = 72
>;
>...
>...
>3253 rows in set (8.00 sec)
>
>Explain says:
>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 | ID | 1 | ID
>| A | NULL | NULL | NULL | | BTREE |
>|
>| Offers_To_Buy | 1 | oldtitle | 1 | oldtitle
>| A | NULL | NULL | NULL | | BTREE |
>|
>| Offers_To_Buy | 1 | user | 1 | userID
>| A | NULL | NULL | NULL | | BTREE |
>|
>| Offers_To_Buy | 1 | date | 1 | deletedate
>| A | NULL | NULL | NULL | | BTREE |
>|
>| Offers_To_Buy | 1 | subcategory | 1 | subcatID
>| A | NULL | NULL | NULL | | BTREE |
>|
>| Offers_To_Buy | 1 | country | 1 | country
>| A | NULL | NULL | NULL | | BTREE |
>|
>| Offers_To_Buy | 1 | source | 1 | source
>| 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 |
>|
>| Offers_To_Buy | 1 | keywords | 1 | keywords
>| NULL | NULL | NULL | NULL | YES | FULLTEXT |
>|
>| Offers_To_Buy | 1 | bid | 1 | bid
>| NULL | NULL | NULL | NULL | | FULLTEXT |
>|
>+---------------+------------+-------------+--------------+-------------
>+-----------+-------------+----------+--------+------+------------+-----
>----+
>11 rows in set (0.00 sec)
>
>
>
>
>
>
>
>
>
>
>
