Need expert advice on complex nested queries

Question

I have 3 queries. I was told that they were potentially inefficient so I was wondering if anyone who is experienced could suggest anything. The logic is somewhat complex so bear with me.

I have two tables: shoutbox, and topic. Topic stores all information on topics that were created, while shoutbox stores all comments pertaining to each topic. Each comment comes with a group labelled by reply_chunk_id. The earliest timestamp is the first comment, while any following with the same reply_chunk_id and a later timestamp are replies. I would like to find the latest comment for each group that was started by the user (made first comment) and if the latest comment was made this month display it.

What I have written achieves that with one problem: all the latest comments are displayed in random order. I would like to organize these groups/latest comments. I really appreciate any advice

Shoutbox

Field        Type
-------------------
id              int(5)           
timestamp       int(11)              
user            varchar(25)      
message         varchar(2000)    
topic_id        varchar(35)     
reply_chunk_id  varchar(35)

Topic

id                    mediumint(8)       
topic_id              varchar(35)            
subject_id            mediumint(8)           
file_name             varchar(35)            
topic_title           varchar(255)           
creator               varchar(25)            
topic_host            varchar(255)           
timestamp             int(11)            
color                 varchar(10)            
mp3                   varchar(75)        
custom_background     varchar(55)            
description           mediumtext                     
content_type          tinyint(1)

Query

$sql="SELECT reply_chunk_id FROM shoutbox 
GROUP BY reply_chunk_id 
HAVING count(*) > 1 
ORDER BY timestamp DESC ";
$stmt16 = $conn->prepare($sql);
$result=$stmt16->execute();
while($row = $stmt16->fetch(PDO::FETCH_ASSOC)){


$sql="SELECT user,reply_chunk_id, MIN(timestamp) AS grp_timestamp
FROM shoutbox WHERE reply_chunk_id=? AND user=?";
$stmt17 = $conn->prepare($sql);
$result=$stmt17->execute(array($row['reply_chunk_id'],$user));
while($row2 = $stmt17->fetch(PDO::FETCH_ASSOC)){


$sql="SELECT t.topic_title, t.content_type, t.subject_id, 
    t.creator, t.description, t.topic_host,
    c1.message, c1.topic_id, c1.user, c1.timestamp AS max
FROM shoutbox c1 
JOIN topic t ON (t.topic_id = c1.topic_id)
WHERE reply_chunk_id = ? AND c1.timestamp > ?
ORDER BY c1.timestamp DESC, c1.id
LIMIT 1";
$stmt18 = $conn->prepare($sql);
$result=$stmt18->execute(array($row2['reply_chunk_id'],$month));
while($row3 = $stmt18->fetch(PDO::FETCH_ASSOC)){

Johan · Accepted Answer

Make the first query:

SELECT reply_chunk_id FROM shoutbox 
GROUP BY reply_chunk_id 
HAVING count(*) > 1 
ORDER BY timestamp DESC

This does the same, but is faster. Make sure you have an index on reply_chunk_id.

The second query:

SELECT user,reply_chunk_id, MIN(timestamp) AS grp_timestamp
FROM shoutbox WHERE reply_chunk_id=? AND user=?

The GROUP BY is unneeded, because only one row gets returned, because of the MIN() and the equality tests.

The third query:

SELECT t.topic_title, t.content_type, t.subject_id, 
    t.creator, t.description, t.topic_host,
    c1.message, c1.topic_id, c1.user, c1.timestamp AS max
FROM shoutbox c1 
JOIN topic t ON (t.topic_id = c1.topic_id)
WHERE reply_chunk_id = ? AND c1.timestamp > ?
ORDER BY c1.timestamp DESC, c1.id
LIMIT 1

Doing it all in one query:

SELECT 
    t.user,t.reply_chunk_id, MIN(t.timestamp) AS grp_timestamp,
    t.topic_title, t.content_type, t.subject_id, 
    t.creator, t.description, t.topic_host,
    c1.message, c1.topic_id, c1.user, c1.timestamp AS max
FROM shoutbox c1 
INNER JOIN topic t ON (t.topic_id = c1.topic_id)
LEFT JOIN shoutbox c2 ON (c1.id = c2.id and c1.timestamp < c2.timestamp)
WHERE c2.timestamp IS NULL AND t.user = ?
GROUP BY t.reply_chunk_id
HAVING count(*) > 1
ORDER BY t.reply_chunk_id

or the equivalent

SELECT 
    t.user,t.reply_chunk_id, MIN(t.timestamp) AS grp_timestamp,
    t.topic_title, t.content_type, t.subject_id, 
    t.creator, t.description, t.topic_host,
    c1.message, c1.topic_id, c1.user, c1.timestamp AS max
FROM shoutbox c1 
INNER JOIN topic t ON (t.topic_id = c1.topic_id)
WHERE c1.timestamp = (SELECT max(timestamp) FROM shoutbox c2 
                      WHERE c2.reply_chunk_id = c1.reply_chunk_id)
  AND t.user = ?
GROUP BY t.reply_chunk_id
HAVING count(*) > 1
ORDER BY t.reply_chunk_id

How does this work?

The group by selects one entry per topic.reply_chunk_id
The left join (c1.id = c2.id and c1.`timestamp` < c2.`timestamp`) + WHERE c2.`timestamp` IS NULL selects only those items from shoutbox which have the highest timestamp. This works because MySQL keeps increasing c1.timestamp to get c2.timestamp to be null as soon as that is true, it c1.timestamp will have reached its maximum value and will select that row within the possible rows to choose from.

If you don't understand point 2, see: http://dev.mysql.com/doc/refman/5.0/en/example-maximum-column-group-row.html

Note that the PDO is autoescaping the fields with backticks

Need expert advice on complex nested queries

Answers (2)

Related Questions