How to improve a MySQL join query speed?

Question

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?

Try running the query with the keyword `EXPLAIN` before, so the db explains how the query is performed and could help in finding a way to optimise it. You can read more [here](https://dev.mysql.com/doc/refman/8.0/en/using-explain.html). — 8ns, Nov 12 '21 at 11:18
There are about 6 different solutions listed for this problem in the following SO question: https://stackoverflow.com/questions/7745609/sql-select-only-rows-with-max-value-on-a-column You can play around with those solutions as well. — Shadow, Nov 12 '21 at 11:28
You can try create another table with selection data. Your table `account_history` it's source logs, if you use for specific grids need create `account_last_activity` where same columns from table `account_history`. — OlehTytarenko, Nov 12 '21 at 17:03
it's like be `INSERT INTO t1 (a,b,c) VALUES (1,2,3) ON DUPLICATE KEY UPDATE c=c+1;` [docs](https://dev.mysql.com/doc/refman/8.0/en/insert-on-duplicate.html) — OlehTytarenko, Nov 12 '21 at 17:05

score 1 · Answer 1 · answered Nov 12 '21 at 17:27

   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

Rick James · Answer 2 · 2021-11-12T16:48:35.707

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".

How to improve a MySQL join query speed?

2 Answers2