Walt, ----- Original Message ----- From: "walt" <[EMAIL PROTECTED]> Newsgroups: mailing.database.mysql Sent: Saturday, October 12, 2002 11:20 AM Subject: innodb not using correct index
> Is there a way to find out what index an sql query is using? I know you can > use explain, but those are just "possible" indexes that the query might use. > I'm trying to find out exacly what execution path it is taking. Something EXPLAIN SELECT ... always tells what index, if any MySQL picked. > similar to Oracle's "set autotrace on;" > > MySql version - MySQL-Max-3.23.52-1 > OS version - RedHat 2.4.18-SGI_XFS_1.1enterprise > > Here is the problem I'm having. > I've got a simple query which is taking about 1 1/2 minutes to run. > > select several_fields from xray > where create_date > '2002-10-03' > and cust_id = 'TEST1'; The optimizer of upcoming 3.23.53 was tuned for these kinds of queries. It might solve your problem. With good luck 3.23.53 is released October 17, 2002. > I have the following indexes. > X_CREATE_DATE(create_date, cust_id). > X_CUST_ID(cust_id, download_file). > > If I run > select several_fields from xray > where create_date > '2002-10-03'; > > It takes less the 10 seconds. > > If I run > select several_fields from xray > where cust_id = 'TEST1'; > > It also takes less than 10 seconds. > > If I run the query on my Oracle test server (sparc 20, dual 60mhz, 160mb ram, > old ass raid with 5400rpm scsi drives), it takes about 10 seconds to return > the 374 rows. ... > Thanks! > sql, query > -- > 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
