And Chris, maybe now you can get rid of some of those single indexes and drop the entire size down quite a bit.
Donny > -----Original Message----- > From: Chris Fossenier [mailto:[EMAIL PROTECTED] > Sent: Tuesday, March 09, 2004 6:48 PM > To: 'Richard Davey'; [EMAIL PROTECTED] > Subject: RE: Optimizing Queries > > Rich, > > Thanks for the email. I created a multi-field index using the fields that > are in the query and the query only took 0.91 seconds. That's better than > 9 > minutes and definitely under 30 seconds. > > Thanks. > > 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] > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
