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)
