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:[EMAIL PROTECTED] > Sent: Friday, December 10, 2004 8:12 PM > To: [EMAIL PROTECTED] > 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) > > > > > > > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
