I often read articles saying to combine statements and send less queries. But you seldom see advise about splitting queries to improve performance. Here is what I came across just the other day:
SELECT IF(`uid_to`=@user, `uid_from`, `uid_to`) as `uid`, `message`, `date`, `uid_to`=@user as received
FROM `beepme_msg`
WHERE (`uid_from`=@user and `uid_to`!=@user) or (`uid_to`=@user and `uid_from`!=@user)
ORDER BY `uid_to`, `date`;
The query gets all messages send and received by the user, filtering our message the user send to himself. This looks like a good query since you’ll get all you data in one call. In reality it will mess up your performance, since it can’t use any indexes and will therefor use a table scan. And as we know, tables scans are slow.
The ‘OR’ statement basically messes this up, since only one index can be used per table. We can see what happens a bit better if we rewrite the query (not changing behaviour):
SELECT IF(`uid_to`=@user, `uid_from`, `uid_to`) as `uid`, `message`, `date`, `uid_to`=@user as received
FROM `beepme_msg`
WHERE (`uid_from`=@user or `uid_to`=@user) and not (`uid_from`=@user and `uid_to`=@user)
ORDER BY `uid_to`, `date`;
We see that (`uid_from`=@user and `uid_to`=@user) is just an additional filter and doesn’t have to much impact. It is clear that neither an index on uid_from or uid_to can be used, because neither can get a set with all records which fit within the criteria. If an index gets to many records, the selection is filtered to get the correct result. But if an index gets to few records, it simply can not be used.
To solve this we need to split up this query and use a UNION SELECT:
SELECT `uid_to` as `uid`, `message`, `date`, 0 AS `received` FROM `beepme_msg` WHERE `uid_from`=@user and `uid_to`!=@user
UNION SELECT `uid_to` as `uid`, `message`, `date`, 1 AS `received` FROM `beepme_msg` WHERE `uid_to`=@user and `uid_from`!=@user
ORDER BY `uid_to`, `date`;
This is a nice example of how common rules like ‘avoid subqueries and union selects’ are not alway true. So keep using your head (and EXPLAIN) while writing queries.