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 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
