I have two tables, users and followers:
Table users:
id INT, PRIMARY_KEY
name VARCHAR
joined INT
This table is indexed on id and joined.
Table 'followers':
user INT
follows INT
This table is indexed on users and follows.
This query finds the names of all users followed by a particular user who joined after a particular time. The results need to be in reverse chronological order.
SELECT u.name
FROM users u, followers f
WHERE f.user = X
AND f.follows = u.id
AND u.joined > 1234
ORDER BY u.joined DESC
Now, when user X has a large number of followers, EXPLAIN gives the following:
id key extra
-----------------------------------
u joined Using where
f follows Using index
So far so good. (The 'using where' is due to some other clauses I've removed for brevity).
But, when user X has a small number of followers, this happens:
id key extra
-----------------------------------
f follows Using temporary, using filesort
u joined Using where
If I omit the ORDER BY, I get:
id key extra
-----------------------------------
f follows
u joined Using where
The MySQL optimiser seems to be inspecting the number of rows it has to deal with, and if its small, doing the
followers table first. It seems to ignore the ORDER BY in its optimisation steps, resulting in a slower query due
to the temporary table.
So (finally), my question is: is it possible to force the order in which MySQL performs table searches, and in the likely event this isn't possible, is there another way to get rid of using temporary?
MySQL DOES offer a clause "STRAIGHT_JOIN" which tells it to do the join between tables in the order you've provided. Since you are looking for a single specific "Follower User" put the follower table up front and join from that... Try something like
SELECT STRAIGHT_JOIN
u.name
from
followers f
join Users u
on f.follows = u.id
and u.joined > 1234
where
f.user = X
order by
u.joined DESC
This should FORCE Starting with the "Followers" table, specific for user ID = X, then join out to the users table as SECONDARY based on the rows returned from f.user = X. Make sure your Followers table has an index where "user" is in the first position (in case your index on both columns was (follows, user), it SHOULD be (user, follows). The smallest granularity on your query basis is the one person inquiring about... that comes in FIRST.