On Thu, Jan 15, 2009 at 09:13:25AM -0500, dariuszs wrote:
table1 - 1,000,000 records table2 - 300,000 records (from table1) table3 - 400,000 records (from table1)
select count(*) from table1 where key27 in (select key27 from table2, table3); (the answer should be 700,000)
If I'm reading this right, it'll be trying to create the Cartesian product of table2 and table3 (i.e. 300,000 * 400,000 rows).
At this point server crashed.
This is a bug that needs to be fixed, but I think your query may want to be something more like: SELECT COUNT(*) FROM table1 WHERE key27 IN ( SELECT key27 FROM table2 UNION SELECT key27 FROM table3); or spelled differently: SELECT COUNT(*) FROM table1 a, ( SELECT key27 FROM table2 UNION SELECT key27 FROM table3) b WHERE a.key27 = b.key27; Hope that helps! -- Sam http://samason.me.uk/