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