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

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