UNION Problem

[Bill Dennis]

I had this problem in 4.1.7, upgraded to 4.1.11, but am still having the problem. If I look at WinXP Task Manager, MySQL is taking 99% of CPU cycles and grabbing more memory every few seconds. The first two of these SQL statements work. The 3rd one, which contains a UNION statement, hangs.

1) select distinct(Metadata.metadataID),Formats.format FROM Metadata
JOIN Formats ON Metadata.MetadataID = Formats.MetadataID
WHERE Metadata.Private='0'
AND Metadata.MetadataID IN
(Select Metadata.MetadataID from metadata
where (title like '%pancreas%'));

This returned about 700 valid rows for me.

2) Select keywords.metadataID FROM keywords where keyword like '%pancreas%';

This also returns about 600 valid rows. But when I combine the two in the SQL below, MySQL never returns. It just sits there consuming 99% of the CPU cycles and reducing the system to a grindingly slow pace:

3) select distinct(Metadata.metadataID),Formats.format FROM Metadata
JOIN Formats ON Metadata.MetadataID = Formats.MetadataID
WHERE Metadata.Private='0'
AND Metadata.MetadataID IN
(Select Metadata.MetadataID from metadata
where (title like '%pancreas%')
UNION Select keywords.metadataID FROM keywords where keyword like
'%pancreas%');

Thanks.