OS: Win2k Server MySQL: 4.1.1 alpha / 4.1.3 beta Table type: INNO DB In my production environment, running the query:
select * from main where id in (select main_id from receipt group by main_id having COUNT(*) > 5) will hang the server - sometimes for over a day, thugh it seems it will eventually complete working on it if given enough time. Currently main contains ~200,000 records and receipt contains ~16,000. Main records with any receipts have an average of ~10 receipts, but most have none. Created a smaller test database: master id int(6) primary autoincrement name varchar(25) (filled with random 10 char strings) detail id int(6) primary autoincrement master_id int(6) index (filled with random ints <= max(master.id)) detail varchar(25) (filled with random 10 char strings) temp id int(6) index Fill master with 1,000 records, detail with 10,000. Clone and fill master with 10,000 records, detail with 100,000. Query: select * from master where master.id in (select master_id from detail group by master_id having COUNT(*) > 2) (small) returns 76 rows in 13 seconds. (large) returns 496 rows in 566 seconds. (COUNT(*) > 15) Tried a two part query, sending the intermediate results to a temporary table: create temporary table t select master_id from detail group by master_id having COUNT(*) > 2; select * from master where master.id in (select master_id from t); drop table t; (small) returns 76 rows in 2.8 seconds. (large) returns 496 rows in 17 seconds. Running the intermediate results into a permanent table: truncate table temp; insert into temp select master_id from detail group by master_id having COUNT(*) > 2; select * from master where master.id in (select id from temp); (small) returns 76 rows in 0.16 seconds. (large) returns 496 rows in 0.17 seconds. Have tried playing around with some of the system variables: query_cache_size, innodb_buffer_pool_size with no real affect. In our production environment (record size is much larger, similar number of records to the large test set), both the nested query and the two-part query using a temporary query hang for indeterminate (> 6 hrs) amounts of time, leaving the use of a permanent table as the only option. Of course, the only real way to manage this is to create a dedicated scratch table for each user of the system, a somewhat onerous workaround. Anyone have an idea on a solution to this? Is there something in setting up for INNO DB that I am missing, or should I file this as a bug? Thanks. - Leo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
