CODEWITHSUNDEEP
mysqlsqlquery-performancesqlperformance
kkica
kkica

Reputation: 4104

How to force the order of the joins to improve query performance in MYSQL?

I have a sql statement

SELECT count(*)
From table1 
inner join table2 on condition1
..
inner join tableN on conditionN-1
inner join problematic_table on tableN.FKColumn= problematic_table.FKColumn

This yields result in 20-25 seconds.

If I run the query like this, it runs faster. In 100ms

select count(*)
from problematic_table where problematic_table.FKColumn in (
select distinct tableN.FKColumn
    From table1 
    inner join table2 on condition1
    ..
    inner join tableN on conditionN-1
)

I want to point out that the joins of tables from table1 to tableN have no result (are empty).

So why is the performance in the first case that bad?

EDIT: When running EXPLAIN the tables are sorted on a different order from the order that I have written in JOIN

EDIT2 So for the first query, the problemati_table join is not run last, but the queries who actually reduce the number of rows to 0 are run last. For the second query is the same order except the problematic_table is at the top with id=1 and select_type=Primary, the others are id=2 and select_type=MATERIALIZED.

So I guess the question becomes how to make the engine run the query in the order I wrote it to?

EDIT3

what might be the case is that the joins conditions that are run last by the engine are TABLE1 and TABLE2 which are of form:

SELECT
FROM TABLE1
INNER JOIN TABLE2 on TABLE1.COLUMN1='constant_string' and TABLE2.COLUMN2='constant_string2'
INNER JOIN ... other tables have proper join conditions between colums of the tables.

EDIT4 Changed the title of the questions to attract others that may face the same issue.

Upvotes: 0

Views: 422

Answers (1)

kkica
kkica

Reputation: 4104

The issue was that the engine was running the joins in an order that was performing badly. I fixed the issue by using the STRAIGHT_JOIN optimizer hint instead of simple INNER JOIN

Upvotes: 1

Related Questions