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)

Hedrick

How have you proven that a -> b -> c would be faster? Also, can you show your indexes? – Macmahon

As for testing which is faster, it can be hard to test that without actually having a large amount of data pre-loaded and simulating a normal load. It's often good to let MySQL do its own optimisation, which becomes increasingly accurate the more data you have (and over time, or running ANALYZE TABLE once or twice), and only force indexes if you run into problems. Such problems do exist though; I have definitely had situations where MySQL's query plan was pathalogically bad but after forcing a certain index and therefore join order it worked nice and efficiently. – Favors

@thomasrutter: If MySQL's optimisation result in a 2-7 sec (depends on load/amount of data) query I have no other choise than using my own optimisations :) AS Hammerite suggested: STRAIGHT_JOIN does what I want and 1 fastened the query a thousand times. – Juryrigged

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.

Brachium

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. That was exactly what I was searching for. Now my explain shows a -> b -> c and I don't need an SUBSELECT. – Juryrigged

Very nice. Strange, though, that STRAIGHT_JOIN can only do an inner join, and not an outer join. forums.mysql.com/read.php?115,565624,565668#msg-565668 tries to explain why (last line), but is simply not correct. The optimizer can and does change the order for some LEFT JOINs just the same, as shown by EXPLAIN, and it seems there is no STRAIGHT_JOIN cure for those. – Beckham

Wow thank you so much! Never heard of this before, and this was the perfect solution to my 10s+ query… – Tranquillity

old but gold, i was under impression that execution plan optimises the query, but in my case it couldn't and this was the perfect answer – Glairy

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.

Favors

Because in some cases you can't modify existing joins yet you can added additional conditions like force index – Turnbull

Answer 3

You can try rewriting in two ways

• bring some of the WHERE condition into JOIN
• introduce subqueries even though they are not necessary

Both things might impact the planner.

First thing to check, though, would be if your stats are up to date.

Macmahon

Adding the result to the JOIN didn't change anything, but I included a subquery to reduce the amount of rows in b. The execution plan still shows c -> b -> a but that seems to work (look at my updated question). – Juryrigged

Adding a condition to the JOIN won't really improve anything. To observe this, pre-pend the query with "explain extended" then following the query add "show warnings;". The show warnings will display the query as "optimized" by the optimization engine. It moves all the JOIN requirements into the nearest-bound WHERE clause... – Polysepalous