Home > front end >  How to improve a MySQL join query speed?
How to improve a MySQL join query speed?

Time:11-14

Table Name : account

AccountId RegDate Name password
A 20211101 Rosa 1234
B 20211102 Daisy 2345
C 20211103 Holly 3456

Table Name : account_history

AccountId LoginTime Os browser
A 202111011303 mac safari
B 202111011406 window chrome
C 202111012507 android chrome
A 202111051903 mac safari
B 202111051806 window chrome
C 202111052707 android chrome
A 202111061603 mac safari
B 202111061406 window chrome
C 202111072007 android chrome
A 202111087303 mac safari
B 202111088406 window chrome
C 202111089507 android chrome

The result I want.

AccountId RegDate LatestLoginTime os
A 20211101 2021202111087303101 202111087303
B 20211102 202111088406 202111088406
C 20211103 202111089507 android

I used

SELECT a.AccountId,a.RegDate,b.LatestLoginTime,b.Os 
FROM account a 
LEFT JOIN (SELECT MAX(LoginTime) as LatestLoginTime,AccountId,Os
           FROM accounthistory GROUP BY AccountId) b
ON a.AccountId = b.AccountId 

But this query is too slow. How can I get the data I want quickly?

CodePudding user response:

These two indexes may help:

account:  INDEX(AccountId,  RegDate)
accounthistory:  INDEX(AccountId,  LatestLoginTime, OS)

But there are issues:

  • Is Os dependent on AccountId? (Cf: only_full_group_by)
  • Why LEFT?

Depending on your answers, there may be a much better way to formulate the query.

Can one account have two different OS values?

Consider the following:

SELECT  a.AccountId,
        b.OS,
        a.RegDate,
        b.LatestLoginTime
    FROM (
        SELECT  MAX(LoginTime) as LatestLoginTime,
                AccountId, OS
            FROM  accounthistory
            GROUP BY  AccountId, OS
         ) AS b  
    JOIN account a  ON a.AccountId = b.AccountId 

If you want just one row per Account, and the OS for that last login, then it is a much more complex query -- Search for "groupwise-maximum".

CodePudding user response:

   SELECT h.AccountId,
          a.RegDate,
          MAX(h.LoginTime) AS LatestLoginTime,
          h.Os
     FROM Account AS a
LEFT JOIN AccountHistory AS h USING (AccountId)
 GROUP BY h.AccountId, a.RegDate, h.Os

With indexes on AccountId in both tables, and LoginTime in AccountHistory.

See sqlfiddle

  • Related