Heikki Tuuri wrote: > 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
Heikki, Thanks for you answer! I found what appears to be a tweak (I'd say BUG but the info is correct) needed with mysql. If I make this query a range on the date, it takes less than a second to return the result even though the ending date I'm using is > the max(date) in the table. Example: mysql> select max(CREATE_DATE) FROM XRAY; +---------------------+ | max(CREATE_DATE) | +---------------------+ | 2002-10-03 20:12:57 | +---------------------+ 1 row in set (0.07 sec) select several_fields from xray where create_date > '2002-10-03' and cust_id = 'TEST1'; Takes about 1 1/2 minutes select several_fields from xray where create_date > '2002-10-03' and create_date < '2002-10-03 23:59:59' and cust_id = 'TEST1'; Takes less than a second. Problem solved! Thanks again Heikki! walt --------------------------------------------------------------------- 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
