mysql 强制继续运行_有没有办法强制执行MySQL？

叁歲伎倆 2022-11-19 08:00 155阅读 0赞

![bd96500e110b49cbb3cd949968f18be7.png][]

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)

解决方案

Perhaps you need to use STRAIGHT\_JOIN.

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.

[bd96500e110b49cbb3cd949968f18be7.png]: /images/20221119/ba9d142a6df74ff29feecc08f197c72a.png