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
