Walt, ----- Original Message ----- From: "walt" <[EMAIL PROTECTED]> Newsgroups: mailing.database.mysql Sent: Tuesday, December 17, 2002 1:58 AM Subject: innodb/mysql slow returning anything other than primary key
> 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. do you have a hanging transaction which prevents purge from removing delete-marked rows? What does SHOW INNODB STATUS say? What does EXPLAIN SELECT ... print for each query? What does SHOW TABLE STATUS FROM databasename LIKE 'tablename' say? For each query also do: run SHOW STATUS and SHOW INNODB STATUS, run the query once, and run SHOW STATUS and SHOW INNODB STATUS again. Send the outputs to me. How many customer ids are there in the table? If there are no matching rows, the query should return in < 100 ms. > 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" Best regards, Heikki Tuuri Innobase Oy --- InnoDB - transactions, row level locking, and foreign key support for MySQL See http://www.innodb.com, download MySQL-Max from http://www.mysql.com --------------------------------------------------------------------- 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
