In MySQL, I need to query the three most recent tasks performed by an employee from a table which includes the employee name, the task, and the timestamp. For example, for employee Adam, the most recent tasks are Filing, Typing, and Cleaning.

My current query returns accurate results, but I'd like some guidance if I have written the query in the most efficient fashion. Any advice would be greatly appreciated.

CREATE TABLE TempData ( Employee VARCHAR(16), Task VARCHAR(16), TaskDate DATE );

INSERT INTO TempData VALUES 
( 'Adam', 'Filing', '2022-09-30' ),
( 'Adam', 'Filing', '2022-09-29' ),
( 'Adam', 'Filing', '2022-09-28' ),
( 'Adam', 'Typing', '2022-09-28' ),
( 'Adam', 'Typing', '2022-09-27' ),
( 'Adam', 'Typing', '2022-09-26' ),
( 'Adam', 'Cleaning', '2022-09-25' ),
( 'Adam', 'Cooking', '2022-09-24' ),
( 'Adam', 'Organizing', '2022-09-23' ),
( 'Bob', 'Typing', '2022-09-27' ),
( 'Bob', 'Baking', '2022-09-25' );


SELECT Task, (  SELECT TaskDate 
                  FROM TempData 
                 WHERE Employee = 'Adam' AND TempData.Task = TaskList.Task 
              ORDER BY TaskDate DESC LIMIT 1 ) MostRecent
FROM ( SELECT DISTINCT Task FROM TempData WHERE Employee = 'Adam') TaskList
ORDER BY MostRecent DESC
LIMIT 3;

CodePudding user response：

Your amalgamation of sub-queries is confusing and convoluted. Why not just something like this?

select Task, max(TaskDate) as MostRecent from TempData 
where Employee = 'Adam'
group by Task 
order by MostRecent desc 
limit 3;

Sub-queries are at least no more efficient than a join - but usually less efficient, especially if they're convoluted, because the sql engine has trouble optimizing them back to joins. Consider the explain select for both our queries.

If you want it to be more efficient you can put an index on the table on Employee.