MySQL JOIN with SUBQUERY very slow

Question

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

Why the fsck would you make a subquery that selects from one table only, with no aggrataion and not even any filtering? That doesn't make sense! — Erich Kitzmueller, Jul 03 '14 at 18:16
Will you only ever have one level of responses (i.e. each question can have a number of responses, but a response can't also be a question)? That's implied by your select statement (i.e. it's non-recursive). If so, I'd split questions and responses into separate tables; that way you'll reduce the amount of data you need to query over. — JohnLBevan, Jul 03 '14 at 18:17
You don't need the order by in the subquery... Ordering is expensive — xQbert, Jul 03 '14 at 18:18
But you're not getting the latest response. You're getting all the responses, ordered by `idTopic`. — Barmar, Jul 03 '14 at 18:21
@JohnLBevan I'm not developing, but I would like to optimize an existing one. I would like to avoid big changes — Simone Nigro, Jul 03 '14 at 18:21
It doesn't matter. You're not limiting the number of rows you're returning in the subquery. — Barmar, Jul 03 '14 at 18:22
See this question for how to get the latest row in a group: http://stackoverflow.com/questions/1313120/retrieving-the-last-record-in-each-group — Barmar, Jul 03 '14 at 18:22
To return something latest, the query should use some aggregation (that is contain the GROUP BY clause). — newtover, Jul 03 '14 at 18:23
@Barmar right, the problem is definitely the large number of records in the subquery .. but I do not know how I can fix it... — Simone Nigro, Jul 03 '14 at 18:24
@Barmar thanks for the link! I will try all the different solutions — Simone Nigro, Jul 03 '14 at 18:44

xQbert · Answer 1 · 2014-07-03T18:39:34.603

2

Assuming the highest IDTopic is the last responses user... and assuming you want to return topics without responses...

Select A.IDTopic, A.IDParent, A.User, A.Title, A.Text, 
case when b.User is null then 'No Response' else B.User end as LastReponseUser
FROM topic A
LEFT JOIN Topic B
 on A.IdTopic = B.IDParent
 and B.IDTopic = (Select max(IDTopic) from Topic 
                  where IDParent=B.IDParent group by IDParent)
WHERE  A.IDParent =0

edited Jul 03 '14 at 18:39

answered Jul 03 '14 at 18:25

xQbert

34,733
2
41
62

Updated to correct issue wtih missing then and limit results to only include parent topics. – xQbert Jul 03 '14 at 18:44
+1 Thank you very much, your answer works on sqlfiddle. Tomorrow I'll try on production db. – Simone Nigro Jul 03 '14 at 18:47

JohnLBevan · Accepted Answer · 2014-07-03T18:35:06.900

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

I apologize if my question was unclear. I think your answer does not work with more than one answer http://sqlfiddle.com/#!2/a39d6/1 — Simone Nigro, Jul 03 '14 at 18:36
+1 Thank you very much, your answer works on sqlfiddle. Tomorrow I'll try on production db. — Simone Nigro, Jul 03 '14 at 18:48

MySQL JOIN with SUBQUERY very slow

2 Answers2