Why does it only use the one index? Chris.
-----Original Message----- From: Richard Davey [mailto:[EMAIL PROTECTED] Sent: Tuesday, March 09, 2004 11:00 AM To: [EMAIL PROTECTED] Subject: Re: Optimizing Queries Hello Chris, Tuesday, March 9, 2004, 4:38:00 PM, you wrote: CF> I'm trying to determine the best way to optimize the query below. CF> Right now it is taking around 9mins and we need it to take no more CF> than 30 seconds (we can get it under 30s on MS SQL): CF> | 1 | SIMPLE | speedlink | ref | CF> idx_state,idx_county,idx_phone,idx_homeowner,idx_hpm,idx_ph,idx_first,idx_la CF> st | idx_state | 3 | const | 2840162 | Using where | Some things that have been recommended to me which may be useful for you: Is the table in question Fixed or Dynamic? Do anything you can, including separating out dynamic length fields to joined tables, to make your main table fixed in length. Your fields appear to be indexed individually rather than collectively, so out of the 8 possible indexes in use, it's only using one of them (state). Try combining common/grouped indexes together based on the type of query you run most often. If MySQL is using the wrong index (or one that isn't as efficient as it could be) force it to use another. I don't know about your table definition, but check to see if you really do need to use the datatype you've currently selected. You can shave MBs from the total table size just by optimising your use of data types. Just some thoughts anyway. -- Best regards, Richard Davey http://www.phpcommunity.org/wiki/296.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
