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]
