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 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'; 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. The mysql server is a PIII 600mhz, 500mb ram, with 2 10K rpm lvd scsi drives attached to an Ultra 160 scsi card and is much faster when doing single where clauses than the oracle test server. To satisfy my curiosty, I wrote a perl script that selected all the cust_id where create_date > '2002-10-03' and then checked the cust_id against "TEST1' and it only took about 15 seconds. I origionally only had create_date in the X_CREATE_DATE index, but I figured adding cust_id along with it would make it faster but it didn't. I'm open to any suggestions or comments. This table 46 columns * 2.2million rows and has 13 indexes (full table scans bring the db to it's knees). It's still a test database so I can try "most" anything to speed this up. 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" --------------------------------------------------------------------- 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
