Is there a way to force MySQL execution order?

Question

I know I can change the way MySQL executes a query by using the FORCE INDEX (abc) keyword. But is there a way to change the execution order?

My query looks like this:

SELECT c.*
FROM table1 a
INNER JOIN table2 b ON a.id = b.table1_id
INNER JOIN table3 c ON b.itemid = c.itemid
WHERE a.itemtype = 1
  AND a.busy = 1
  AND b.something = 0
  AND b.acolumn = 2
  AND c.itemid = 123456

I have a key for every relation/constraint that I use. If I run explain on this statement I see that mysql starts querying c first.

id    select_type    table    type
1     SIMPLE         c        ref
2     SIMPLE         b        ref
3     SIMPLE         a        eq_ref

However, I know that querying in the order a -> b -> c would be faster (I have proven that) Is there a way to tell mysql to use a specific order?

Update: That's how I know that a -> b -> c is faster.

The above query takes 1.9 seconds to complete and returns 7 rows. If I change the query to

SELECT c.*
FROM table1 a
INNER JOIN table2 b ON a.id = b.table1_id
INNER JOIN table3 c ON b.itemid = c.itemid
WHERE a.itemtype = 1
  AND a.busy = 1
  AND b.something = 0
  AND b.acolumn = 2
HAVING c.itemid = 123456

the query completes in 0.01 seconds (Without using having I get 10.000 rows). However that is not a elegant solution because this query is a simplified example. In the real world I have joins from c to other tables. Since HAVING is a filter that is executed on the entire result it would mean that I would pull some magnitues more records from the db than nescessary.

Edit2: Just some information:

The variable part in this query is c.itemid. Everything else are fixed values that don't change.
Indexes are setup fine and mysql chooses the right ones for me
- between a and b there is a 1:n relation (index PRIMARY is used)
- between b and c there is a many to many relation (index IDX_ITEMID is used)

the point is that mysql should start querying table a and work it's way down to c and not the other way round. Any change to achive that.

Solution: Not exactly what I wanted but this seems to work:

SELECT c.*
FROM table1 a
INNER JOIN table2 b ON a.id = b.table1_id
INNER JOIN table3 c ON b.itemid = c.itemid
WHERE a.itemtype = 1
  AND a.busy = 1
  AND b.something = 0
  AND b.acolumn = 2
  AND c.itemid = 123456
  AND f.id IN (
         SELECT DISTINCT table2.id FROM table1
         INNER JOIN table2 ON table1.id = table2.table1_id
         WHERE table1.itemtype = 1 AND table1.busy = 1)

Is there a way to force MySQL execution order?

3 Answers