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
_______________________________________________
luv-main mailing list
[email protected]
https://lists.luv.asn.au/cgi-bin/mailman/listinfo/luv-main
