I have a people table with basic contact info in. I have a bunch of criteria that can select Id values (fields called pid) from the people table.
I want records that match ((criterion 1 or criterion 2) and criterion 3).
I want the criteria statements to add columns to the results.
I need a standard way to assemble the queries because I'm making a 'search builder'; so I can't optimise every combination. It can be assumed that each criterion will return zero or one row.
EG1: I first tried
SELECT people.* FROM people LEFT JOIN (criterion1 SELECT) c1 ON people.id=c1.pid LEFT JOIN (criterion2 SELECT) c2 ON people.id=c2.pid, (criterion3 SELECT) c3 WHERE people.id=c3.pid AND ( c1.pid IS NOT NULL OR c2.pid IS NOT NULL );
This was far too slow - it never actually returned records in the minute I allowed it!
EG2:Next I tried
SELECT people.* FROM people, ( (criterion1 SELECT) UNION (criterion2 SELECT) ) c12 WHERE people.id=c12.pid AND people.id IN (criterion3 SELECT)
This one returns 2000+ rows in 0.6s, which is acceptable. But you can't add any columns from criterion 3, and can't access columns from c1 and c2 either, really, since one will mask another.
EG3:Out of interest, I moved the c12 into the WHERE clause:
SELECT people.* FROM people WHERE id IN (criterion3 SELECT) AND id IN ( (criterion1 SELECT) UNION (criterion2 SELECT) )
but again, this threw it for well over a minute before I killed the query.
EG4: So I returned to the EG2 code, and did this ugly thing:
SELECT * FROM ( all the EG2 SQL ) src LEFT JOIN (criterion1 SELECT) c1 ON src.id=c1.pid LEFT JOIN (criterion2 SELECT) c2 ON src.id=c2.pid
So the c1 and c2 queries are added in twice! This returns the (same number of) results, with the extra fields, in 6s. Too slow, really, but at least it runs. And really ugly!
Can anyone give me any pointers to explain any of this? Why is EG3 massively faster than EG1/EG4 when they're all doing the same thing? Why should running the queries twice (EG4) be faster than running them once (EG1)?!
EDIT: Using MySQL 5.1, InnoDB tables.