I've run into an interesting problem. I have a large innodb table (2274962 
rows, 46 columns, 2 datafiles - 4.5GB total). 
When I run a query that uses a clustered_index in the where clause and the 
data I'm selecting is not the primary key of the table,  it takes up to 2 1/2 
minutes to return zero results (the result is correct). If I select the 
primary key, it takes 2 1/2 seconds to return zero results.

For example:
create table xray (
           trans_id  int not null,
           customer_id char(25) not null,
           customer_last_name char(25),
           UNIQUE INDEX trans_idx (trans_id),
          INDEX cus_id (customer_id ) );
           type=INNODB;

SELECT customer_last_name  FROM XRAY
WHERE customer_id = '12345';

This takes ~ 2 1/2 minutes to return 0 results.

SELECT trans_id   FROM XRAY
WHERE customer_id = '12345';

This takes ~ 2 1/2 SECONDS.

From what I understand, clustered indexes contain the primary key as well as 
the indexed data and return that key as a "pointer" to the row where the data 
being selected is located. This explains why selecting the key is so quick, 
BUT if no values are found in the index that match the where clause, why is 
it taking so long?

I've run explain and it's telling me the database is using the correct index. 
I've even added USE INDEX to sql query with no improvement.

The machine is an AMD  Athlon XP 2000 with 1GB of ram.
Mysql version is 3.23.53
OS = Linux 2.4.18-SGI_XFS_1.1enterprise

Thanks for any ideas or suggestions!
-- 
Walter Anthony
System Administrator
National Electronic Attachment
Atlanta, Georgia 
1-800-782-5150 ext. 1608
 "If it's not broke....tweak it"

---------------------------------------------------------------------
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/           (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Reply via email to