``` <?php $sql = " SELECT u.id, u.username, t.mostRecent FROM ( SELECT IF(sentTo = {$_SESSION['id']}, sentBy, sentTo) chatWith, MAX(date) mostRecent FROM dms WHERE sentTo = {$_SESSION['id']} OR sentBy = {$_SESSION['id']} GROUP BY chatWith ) t JOIN users u ON t.chatWith = u.id ORDER BY t.mostRecent DESC"; $result = mysqli_query($conn, $sql); if (mysqli_num_rows($result) > 0) { while ($row = mysqli_fetch_assoc($result)) { echo "<a href='dms.php?talkingTo={$row['id']}'>{$row['username']}</a><br>"; } } else { echo "<p>It's empty</p>"; } ``` ``` /* groupwise max */ EXPLAIN SELECT u.id, u.username, t.mostRecent FROM ( SELECT IF(sentTo = 1, sentBy, sentTo) chatWith, MAX(date) mostRecent FROM dms WHERE sentTo = 1 OR sentBy = 1 GROUP BY chatWith ) t JOIN users u ON t.chatWith = u.id ORDER BY t.mostRecent DESC; # Serverside execution time: 0.001s # Rows examined: 66 # Rows returned: 22 ``` ``` /* correlated subquery 1 */ EXPLAIN SELECT m.* FROM dms m WHERE 1 in (sentBy, sentTo) AND m.date = (SELECT MAX(m2.date) FROM dms m2 WHERE (m2.sentBy = m.sentBy AND m2.sentTo = m.sentTo) OR (m2.sentBy = m.sentTo AND m2.sentTo = m.sentBy) ) ORDER BY m.date DESC; # Serverside execution time: 0.122s # Rows examined: 440022 # Rows returned: 22 ``` ``` /* correlated subquery 2 */ EXPLAIN SELECT m.* FROM dms m WHERE (sentTo = 1 OR sentBy = 1) AND m.date = (SELECT MAX(m2.date) FROM dms m2 WHERE (m2.sentBy = m.sentBy AND m2.sentTo = m.sentTo) OR (m2.sentBy = m.sentTo AND m2.sentTo = m.sentBy) ) ORDER BY m.date DESC; # Serverside execution time: 0.122s # Rows examined: 430022 # Rows returned: 22 ```