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) 

Answer 1

Perhaps you need to use STRAIGHT_JOIN.

http://dev.mysql.com/doc/refman/5.0/en/join.html

STRAIGHT_JOIN is similar to JOIN, except that the left table is always read before the right table. This can be used for those (few) cases for which the join optimizer puts the tables in the wrong order.

Answer 2

You can use FORCE INDEX to force the execution order, and I've done that before.

If you think about it, there's usually only one order you could query tables in for any index you pick.

In this case, if you want MySQL to start querying a first, make sure the index you force on b is one that contains b.table1_id. MySQL will only be able to use that index if it's already queried a first.