CODEWITHSUNDEEP
mysqlquery-optimization
nafas
nafas

Reputation: 5423

How to optimize mysql on left join

I try to explain a very high level

I have two complex SELECT queries(for the sake of example I reduce the queries to the following):

SELECT id, t3_id FROM  t1;
SELECT t3_id, MAX(added) as last FROM t2 GROUP BY t3_id;

query 1 returns 16k rows and query 2 returns 15k

each queries individually takes less than 1 second to compute

However what I need is to sort the results using column added of query 2, when I try to use LEFT join 

SELECT 
    t1.id, t1.t3_Id
FROM
    t1
        LEFT JOIN
    (SELECT t3_id, MAX(added) as last FROM t2 GROUP BY t3_id) AS t_t2 
        ON t_t2.t3_id = t1.t3_id
GROUP BY t1.t3_id
ORDER BY t_t2.last

However, the execution time goes up to over a 1 minute.

I like to understand the reason

what is the cause of such a huge explosion?

NOTE:

ALL the used columns on every table have been indexed e.g. :

EDIT1

after @Tim Biegeleisen suggestion, I change the query to the following now the query is executing in about 16 seconds. If I remove the ORDER BY it query gets executed in less than 1 seconds. The problem is that ORDER BY the sole reason for this.

SELECT 
    t1.id, t1.t3_Id
FROM
    t1
        LEFT JOIN
    t2 ON t2.t3_id = t1.t3_id

GROUP BY t1.t3_id
ORDER BY MAX(t2.added)

Upvotes: 1

Views: 1457

Answers (3)

Fabio Vaz
Fabio Vaz

Reputation: 31

I think you could try to order everything after the records are available. Maybe:

select * from (
select * from 
(select t3_id,max(t1_id) from t1 group by t3_id) as t1
left join (select t3_id,max(added) as last from t2 group by t3_id) as t2 
on t1.t3_id = t2.t3_id ) as xx
order by last

Upvotes: 0

user1474514
user1474514

Reputation: 9

The correct query should be:

SELECT
  t1.id,
  t1.t3_Id,
  MAX(t2.added) as last
FROM t1
  LEFT JOIN t2 on t1.t3_Id = t2.t3_Id
GROUP BY t2.t3_id
ORDER BY last;

This is happen because a temp table is generating on each record.

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521053

Even though table t2 has an index on column t3_id, when you join t1 you are actually joining to a derived table, which either can't use the index, or can't use it completely effectively. Since t1 has 16K rows and you are doing a LEFT JOIN, this means the database engine will need to scan the entire derived table for each record in t1.

You should use MySQL's EXPLAIN to see what the exact execution strategy is, but my suspicion is that the derived table is what is slowing you down.

Upvotes: 1

Related Questions