All, the tables are defined as MyISAM. In fact, I left last night and the Query was still running the convert HEAP to MyISAM!!!! Somethings definately wrong. I have indices on Name.key, Location.key and Location.cc
the results of the explain : id| select_type| table | type | possible keys | key |key_len| ref |rows | Extra ------------------------------------------------------------------------ ------------------- 1 | SIMPLE | LOCATION|range |PRIMARY,LOC_CC_IDX|LOC_CC_IDX |2 |NULL |55248| 1 | SIMPLE | NAME |ref |NAME_KEY_IDX |NAME_KEY_IDX|15 |LOC_IDX|2 | It looks like it's using the correct indices....I don't know what else to do... -----Original Message----- From: Jeremy Zawodny [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 27, 2003 12:23 AM To: Twibell, Cory L Cc: [EMAIL PROTECTED] Subject: Re: Table Query taking WAY TO LONG...HELP PLEASE!!! On Tue, Aug 26, 2003 at 07:38:04PM -0600, Twibell, Cory L wrote: > All, > > I have a query that is inner joined with another table based on > country codes Select distinct Name.* from Name inner join Location > on Location.key = Name.key and Location.cc in ('<list of countries > here>'); > > The problem is when I have more than 2 country codes, the query > takes forever... When I show processlist, it says converting HEAP > to MyISAM ( this takes over an hour )... I've tried bumping up > max_heap_table_size to 128M and tmp_table_size to 128M, but that > only seems to delay the converting HEAP to MyISAM message.... > > Name contains about 3 million records and Location contains about 1.5 million records. The table is a HEAP table? I don't think the optimizer knows how to make an IN(...) query in a HEAP table fast. Have you tried this with a MyISAM table? It should be pretty fast, assuming you're pulling back less than 1/4th of the rows with that query. Can you show us the EXPLAIN output from the query? Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! <[EMAIL PROTECTED]> | http://jeremy.zawodny.com/ MySQL 4.0.13: up 25 days, processed 1,141,718,690 queries (517/sec. avg) -- 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]
