MySQL JOIN with SUBQUERY very slow

Simone Nigro Published at Dev

Simone Nigro

I have a forum and I would like to see the latest topics with the author's name and the last user who answered

Table Topic (forum)

| idTopic | IdParent | User | Title | Text             |
--------------------------------------------------------
| 1       | 0        | Max  | Help! | i need somebody  | 
--------------------------------------------------------
| 2       | 1        | Leo  |       | What?!           |

Query:

SELECT 
    Question.*,
    Response.User AS LastResponseUser 
FROM Topic AS Question
LEFT JOIN (
   SELECT User, IdParent  
   FROM Topic 
   ORDER BY idTopic DESC
) AS Response 
    ON ( Response.IdParent = Question.idTopic )
WHERE Question.IdParent = 0
GROUP BY Question.idTopic
ORDER BY Question.idTopic DESC

Output:

| idTopic | IdParent | User | Title | Text             | LastResponseUser |
---------------------------------------------------------------------------
| 1       | 0        | Max  | Help! | i need somebody  | Leo              |
---------------------------------------------------------------------------

Example: http://sqlfiddle.com/#!2/22f72/4

The query works, but is very slow (more or less 0.90 seconds over 25'000 record).

How can I make it faster?

UPDATE

comparison between the proposed solutions

http://sqlfiddle.com/#!2/94068/22

JohnLBevan

If using your current schema, I'd recommend adding indexes (particularly a clustered index (primary key)) and simplifying your SQL to let mySQL do the work of optimising the statement, rather than forcing it to run a subquery, sort the results, then run the main query.

CREATE TABLE Topic (
  idTopic  INT
  ,IdParent INT
  ,User     VARCHAR(100)
  ,Title    VARCHAR(255)
  ,Text     VARCHAR(255)       
  ,CONSTRAINT Topic_PK PRIMARY KEY (idTopic) 
  ,CONSTRAINT Topic_idTopic_UK UNIQUE (idTopic) 
  ,INDEX Topic_idParentIdTopic_IX (idParent, idTopic)
);


INSERT INTO Topic (idTopic, IdParent, User, Title, Text) VALUES 
(1, 0, 'Max', 'Help!', 'i need somebody'),
(2, 1, 'Leo', '', 'What!?');


SELECT Question.*
, Response.User AS LastResponseUser 
FROM Topic AS Question
LEFT JOIN Topic AS Response 
  ON Response.IdParent = Question.idTopic
WHERE Question.IdParent = 0
order by Question.idTopic
;

http://sqlfiddle.com/#!2/7f1bc/1

Update

In the comments you mentioned you only want the most recent response. For that, try this:

SELECT Question.*
, Response.User AS LastResponseUser 
FROM Topic AS Question
LEFT JOIN (
  select a.user, a.idParent 
  from Topic as a
  left join Topic as b
  on b.idParent = a.idParent
  and b.idTopic > a.idTopic
  where b.idTopic is null 
) AS Response 
  ON Response.IdParent = Question.idTopic
WHERE Question.IdParent = 0
order by Question.idTopic
;

http://sqlfiddle.com/#!2/7f1bc/3

Collected from the Internet

Please contact [email protected] to delete if infringement.