List:General Discussion« Previous MessageNext Message »
From:Donny Simonton Date:December 11 2004 5:05am
Subject:RE: Why is this simple query so slow?
View as plain text  
Aaron,
Three things.

1.  Do a "show create table Offers_To_Buy"
2.  And why in the world would you have force index(scdd) when your where
clause is on subcatID?  If you can explain what you are trying to do, I'm
sure many people can help you get exactly what you are looking for.
3.  Why so many indexes?  Do you search on every one of those fields?  If
not, then you are probably wasting diskspace and speed.

Donny

> -----Original Message-----
> From: Aaron [mailto:wigsy@stripped]
> Sent: Friday, December 10, 2004 8:12 PM
> To: mysql@stripped
> Subject: Why is this simple query so slow?
> 
> 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