Table Name : account

Table Name : account_history

The result I want.

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