Re: innodb/mysql slow returning anything other than primary key
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 broketweak 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
Re: innodb/mysql slow returning anything other than primary key
Walt, you can also test 3.23.54. The following bug fix may be relevant here: MySQL/InnoDB-3.23.54, December 12, 2002 Fixed a bug: the InnoDB range estimator greatly exaggerated the size of a short index range if the paths to the endpoints of the range in the index tree happened to branch already in the root. This could cause unnecessary table scans in SQL queries. ... Regards, Heikki - Original Message - From: Heikki Tuuri [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, December 18, 2002 1:33 AM Subject: Re: innodb/mysql slow returning anything other than primary key 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 broketweak 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
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. 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 broketweak 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
