Optimizing query with ORDER BY on DATETIME column

Question

The following query depends on table with ~4k rows in links table and ~40k rows in comments table and currently takes about 0.2s which seems to be quite slow considering that there isn't that much data.

SELECT
    t1.id, t1.url, t1.dateAdded
FROM links AS t1 LEFT JOIN
comments AS t2
ON (t1.id = t2.linkId)
WHERE
    COALESCE(t2.dateAdded, t1.dateAdded) <= "2020-03-22 20:04:45"
GROUP BY t1.id
ORDER BY
    COALESCE(
        (
            SELECT
                MAX(dateAdded)
            FROM comments
            WHERE
                linkId = t1.id AND
                dateAdded <= "2020-03-22 20:04:45"
        ),
        t1.dateAdded
    ) DESC,
    t1.id DESC
    LIMIT 10

t1.id is a primary key, t2.linkId is foreign key; I've also tried adding index for dateAdded in both tables but that doesn't seem to help.

Just to identify the bottleneck, I dumbed down the query to the following and noticed that when ordering by t1.dateAdded the query takes 0.12s while when ordering by t1.id it takes only 0.003s

SELECT
    t1.id, t1.url, t1.dateAdded
FROM links AS t1 LEFT JOIN
comments AS t2
ON (t1.id = t2.linkId)
WHERE
    COALESCE(t2.dateAdded, t1.dateAdded) <= "2020-03-22 20:04:45"
GROUP BY t1.id
ORDER BY
    t1.id DESC -- here I tried both t1.dateAdded and t1.id

I therefore tried to find the difference by using EXPLAIN and it seems that the only difference is in the Extra field where for ORDER BY t1.id it is empty and for ORDER BY t1.dateAdded it is Using temporary; Using filesort (note that I have index on t1.dateAdded). Unfortunately, I'm kind of stuck at interpreting what this means and, in general, how to optimize the original query. Note that id is INT(10) and dateAdded is DATETIME.

In general, the goal I'd like to achieve is to order links such that the newest links or links with newest comments are at the top where "newest" means relative to the time provided (i.e. don't consider links/comments added after that).

Thanks in advance for any help or hints

EDIT: Adding more details

EXPLAIN for dumbed down query with t1.id

+------+-------------+-------+-------+---------------+------------+---------+--------------+------+-------------+
| id   | select_type | table | type  | possible_keys | key        | key_len | ref          | rows | Extra       |
+------+-------------+-------+-------+---------------+------------+---------+--------------+------+-------------+
|    1 | SIMPLE      | t1    | index | NULL          | PRIMARY    | 4       | NULL         | 3674 |             |
|    1 | SIMPLE      | t2    | ref   | fk_link_id    | fk_link_id | 5       | db1.t1.id    |    8 | Using where |
+------+-------------+-------+-------+---------------+------------+---------+--------------+------+-------------+

EXPLAIN for dumbed down query with t1.dateAdded

+------+-------------+-------+-------+---------------+------------+---------+--------------+------+---------------------------------+
| id   | select_type | table | type  | possible_keys | key        | key_len | ref          | rows | Extra                           |
+------+-------------+-------+-------+---------------+------------+---------+--------------+------+---------------------------------+
|    1 | SIMPLE      | t1    | index | NULL          | PRIMARY    | 4       | NULL         | 3674 | Using temporary; Using filesort |
|    1 | SIMPLE      | t2    | ref   | fk_link_id    | fk_link_id | 5       | db1.t1.id    |    8 | Using where                     |
+------+-------------+-------+-------+---------------+------------+---------+--------------+------+---------------------------------+

Info about links table:

CREATE TABLE `links` (
  `id` int(10) UNSIGNED NOT NULL,
  `url` varchar(2083) CHARACTER SET utf8mb4 NOT NULL,
  `dateAdded` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

ALTER TABLE `links`
  ADD PRIMARY KEY (`id`),
  ADD KEY `dateAdded` (`dateAdded`);

info about comments table:

CREATE TABLE `comments` (
  `id` int(10) UNSIGNED NOT NULL,
  `linkId` int(10) UNSIGNED DEFAULT NULL,
  `userId` int(10) UNSIGNED NOT NULL,
  `content` varchar(2000) CHARACTER SET utf8mb4 NOT NULL,
  `dateAdded` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

ALTER TABLE `comments`
  ADD PRIMARY KEY (`id`),
  ADD KEY `fk_link_id` (`linkId`);

ALTER TABLE `comments`
  ADD CONSTRAINT `fk_link_id` FOREIGN KEY (`linkId`) REFERENCES `links` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

user_1863229 · Answer

I can start off by pointing out that the GROUP BY in your query is unnecessary (though not wrong) because you are not selecting any aggregates. Beyond this, I feel that you would be making your life easier by just using MAX() as an analytic function, and then ordering by that. Consider this version:

WITH cte AS (
    SELECT t1.id, t1.url, t1.dateAdded,
        MAX(t2.dateAdded) OVER (PARTITION BY t1.id) maxDateAdded
    FROM links AS t1
    LEFT JOIN comments AS t2 ON t1.id = t2.linkId
    WHERE
        (t2.dateAdded IS NOT NULL AND t2.dateAdded <= '2020-03-22 20:04:45') OR
        (t2.dateAdded IS NULL AND t1.dateAdded <= '2020-03-22 20:04:45')
)

SELECT id, url, dateAdded
FROM cte
ORDER BY maxDateAdded DESC, t1.id DESC
LIMIT 10;

This answer assumes you are using MySQL 8+. It can be rewritten for earlier versions of MySQL with a little more effort.

As for optimizing the above query, the following indices might help:

CREATE INDEX idx2 ON comments (linkID, dateAdded);
CREATE INDEX idx1 ON links (dateAdded, url, id);

These indices, if used, would speed up the join, and also would allow the call to MAX to evaluate quickly. Note that I have rewritten the WHERE clause to be sortable, avoiding the call to COALESCE.

Optimizing query with ORDER BY on DATETIME column

1 Answers