Hi There, I am working on a query that I have used both joins and sub queries to try to make into something efficient. I have used the EXPLAIN command to make sure I am taking advantage of indexes and I have added them where they are lacking. The structure of the query looks like this: People_table <--- where the rows I want to show on the screen come from (name, address, etc) - Notes <--- One Person may have many notes, using date range search, also using INT search, and this is using FULLTEXT IN BOOLEAN MODE searching - Profile <--- This is a single text column, this is using FULLTEXT IN BOOLEAN MODE searching - People <--- Filtering by state, country, status, other ENUM / INT flags I have tried (JOINS): SELECT FROM People JOIN Notes JOIN Profile WHERE People Criteria AND Notes Criteria AND Profile Criteria And tried (SUB QUERIES) SELECT FROM People WHERE People.id IN (SELECT P.id FROM NOTES WHERE Notes Criteria AND Pid IN (SELECT P.id FROM Profile WHERE Profile Criteria AND P.id IN (SELECT P.id FROM People WHERE People Criteria) So far I have killed the process after a few minutes as it just takes too long. I noticed temporary tables were being created so I enlarge the join buffer pool in my.cnf which cleared some of the feedback from the EXPLAIN. (...and tried a few others...). Then there is this: - I can do the 3 queries in PHP independently and then use this to get a list of ID's at the application layer to send a 4th query to get the results and it only takes a few seconds. A FEW SECONDS! Questions: - My gut tells me that this should be most efficiently handled at the application layer - How can 3 sub queries take more time than 3 independent queries? - The "People Criteria" is all ENUMS and INT's which are indexed - I though by making this the inner most subquery that it would filter out the most rows before the slower FULLTEXT queries kicked in - is this correct? - JOINS vs SUBQUERIES - is there are "rule"? Does this "rule" get pushed aside when real world testing proves the user experience at the application layer is better by combining multiple SQL quires as opposed to handing it off to the MySQL server? Ideas? Thanks in advance. Cheers P