List:General Discussion« Previous MessageNext Message »
From:Aaron Date:December 11 2004 2:12am
Subject:Why is this simple query so slow?
View as plain text  
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)
 
 
 
 
 
 
 
 


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