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. Right now CF> it is taking around 9mins and we need it to take no more than 30 seconds (we CF> 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]
