mysql optimize left join with subquery

Question

I'm concerned about performance.

It is possibile to optimize the following mysql query?

SELECT u.name, t2.transactions, o2.orders FROM users AS u

LEFT JOIN (
    SELECT t.aid AS tuid, SUM( IF( t.status = 1, t.amount, 0 ) ) AS transactions
    FROM transactions AS t 
    WHERE ( t.datetime BETWEEN ('2018-01-01 00:00:00') AND ( '2020-01-01 23:59:59' ) ) GROUP BY t.aid
) AS t2 ON tuid = u.id 

LEFT JOIN (
    SELECT o.aid AS ouid, SUM(o.net) AS orders FROM orders AS o 
    WHERE ( o.date BETWEEN ('2018-01-01 00:00:00') AND ( '2020-01-01 23:59:59' ) ) GROUP BY o.aid 
) AS o2 ON ouid = u.id

WHERE u.status = 1
ORDER BY t2.transactions DESC

basically I need to sum users' data from multiple tables (and be able to order them)

Can you share some short and testable excerpt with dummy data for your query in sqlfiddle.com ? — RomanPerekhrest
– RomanPerekhrest, Commented Oct 17, 2019 at 11:51

O. Jones · Accepted Answer · 2019-10-17 11:52:35Z

2

There's no obvious query-performance antipattern in your query. Performance pretty much depends on the performance of the two subqueries with group by clauses.

Let's take a look at one of them to find some improvements.

SELECT t.aid AS tuid, 
       SUM( IF( t.status = 1, t.amount, 0 ) ) AS transactions
  FROM afs_transactions AS t 
 WHERE t.datetime BETWEEN '2018-01-01 00:00:00' AND '2020-01-01 23:59:59'
 GROUP BY t.aid

This will be OK if you have an index on afs_transactions.datetime.

But the whole subquery can be rewritten

SELECT t.aid AS tuid, 
       SUM( t.amount ) AS transactions
  FROM afs_transactions AS t 
 WHERE t.datetime BETWEEN '2018-01-01 00:00:00' AND '2020-01-01 23:59:59'
   AND t.status = 1
 GROUP BY t.aid

This query will take advantage of a compound index on (status, datetime). If you have many rows with status values not equal to 1, and you have the compound index, the rewritten query will be faster.

Pro tip: BETWEEN for datetime values is generally a poor choice, because, well, 59:59. Try using < rather than BETWEEN's <= for the end of the range.

 WHERE t.datetime >= '2018-01-01'
   AND t.datetime <  '2020-01-02'   /* notice, it's the day after the range */

answered Oct 17, 2019 at 11:52

O. Jones

110k17 gold badges135 silver badges188 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

James Over a year ago

As you have mentioned t.datetime < '2020-01-02' would that mean if there were records on 2020-01-02 00:00:00 then the result will be different or not equal?

O. Jones Over a year ago

WHERE t.datetime < '2020-01-02' chooses all records before the first moment of 2-Jan-2020. That is, it does not choose a record with the datetime value of 2020-01-02 00:00:00 . On the other hand WHERE t.datetime BETWEEN something AND '2020-01-02' will choose that record; WHERE col BETWEEN a AND b means WHERE col >= a AND col <= b, using >= and <= rather than >= and <.

James Over a year ago

@o Thanks, so it's better to use comparison operator than between.

Rick James Over a year ago

Another benefit: That coding pattern works for DATE, DATETIME, DATETIME(6), etc without any change. And it lets you avoid computing leap years, etc: '2018-01-01' + INTERVAL 2 YEAR`. And it avoids including all of Jan 1 2020, which you probably did not want!.

Rick James · Accepted Answer · 2019-10-18 23:48:51Z

Multiple JOIN ( SELECT ... ) used to be a performance killer (pre 5.6). Now it may be a performance problem.

The alternative is

SELECT u.name,
       ( SELECT ... WHERE ...=u.id ) AS transactions,
       ( SELECT ... WHERE ...=u.id ) AS orders
    FROM users AS u
    WHERE  u.status = 1
    ORDER BY  transactions DESC

The first subquery is a correlated subquery and it looks like

       ( SELECT SUM( IF(status = 1, amount, 0)
            FROM  transactions
            WHERE  aid = u.id
              AND  datetime >= '2018-01-01'
              AND  datetime  < '2018-01-01' + INTERVAL 2 YEAR`
       ) AS transactions

(The other one is similar.)

Indexes:

users:         INDEX(status, name, id)   -- "covering"
transactions:  INDEX(aid, datetime)
orders:        INDEX(aid, date)  or  INDEX(aid, date, net)

Collectives™ on Stack Overflow

mysql optimize left join with subquery

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related