0

I have system that allows comments posted by users and tracks which users seen which comments. The read/unread state is tracked within comments_seen table. Surely enough, it grow tremendously.

My goal is to move from comments_seen table to comments_unseen. Read/Unread status is only tracked for comment author and users in admin group (eg. admins see all new comment on site and should react respectively, while users only see answers to their comments or comments to their orders).

Current comments_seen table is almost 36M rows, while it's current definition is as simple as

CREATE TABLE comments_seen
(
    user_id    int(10) NOT NULL,
    comment_id int(10) NOT NULL,
    CONSTRAINT user_id
        UNIQUE (user_id, comment_id)
)

The comments_unseen table could be very much the same.

What would be your suggestions on my steps to moving from one table design to another? Any triggers to keep data consistent? Any deployment/migration steps? All queries in code could be rewritten at once, I guess, so time to support both tables can be minimized to, say, seconds or minutes.

The overall application load isn't very high and everything could be done in night times for most users to not affect performance or UX. Any thought, suggestions and shared experience is very much appreciated.

The DB is MySQL 5.5

UPDATE 2021-04-01: As pointed out in comments 35M rows isn't huge, but the culprit must be the query. So here's the slowest query (as reported by Percona's pt-query-digest):

SELECT
            SUM(`is_deleted` IS NOT TRUE) AS `total_count`
            , SUM(`parent_id` = 0) AS `branch_count`
            , SUM(`parent_id` = 0 AND `is_can_answer`) AS `opened_branch_count`
            , SUM(`parent_id` = 0 AND NOT `is_can_answer`) AS `closed_branch_count`
            , SUM(`cs`.`comment_id` IS NULL AND `is_deleted` IS NOT TRUE) AS `new_count` , SUM(`cs`.`comment_id` IS NULL AND `is_deleted` IS NOT TRUE AND `module` = 5) AS `order_comment_count` , SUM(`cs`.`comment_id` IS NULL AND `is_deleted` IS NOT TRUE AND `module` = 4) AS `items_comment_count` 
        FROM (
                            (
                                SELECT DISTINCT
                                    5 AS `module`
                                    , 'm' AS `item_type`
                                    , `co`.`id` AS `item_id`
                                FROM `cart_orders` AS `co`
                                WHERE `co`.`author` = 65
                                OR `co`.`manager_id` = 65
                                
                            )
                            UNION
                            (
                                SELECT DISTINCT
                                    5 AS `module`
                                    , 'i' AS `item_type`
                                    , `pr`.`id` AS `item_id`
                                FROM `cart_orders` AS `co`
                                    JOIN `cart_package` AS `cp`
                                        ON `cp`.`order_id` = `co`.`id`
                                    JOIN `pictures_relations` AS `pr`
                                        ON `pr`.`module_id` = 5
                                            AND `pr`.`item_id` = `cp`.`id`
                                            AND `pr`.`item_type` = 'opkg'
                                            AND `pr`.`is_file` IS FALSE
                                WHERE `co`.`author` = 65
                                OR `co`.`manager_id` = 65
                                
                            )
                            UNION
                            (
                                SELECT DISTINCT
                                    4 AS `module`
                                    , 'm' AS `item_type`
                                    , `cat`.`id` AS `item_id`
                                FROM `catalog` AS `cat`
                                    INNER JOIN (
                                        `cart_positions` AS `cp`
                                        JOIN `cart_orders` AS `co`
                                            ON `cp`.`order_id` = `co`.`id`
                                    )
                                        ON `cp`.`cat_id` = `cat`.`id`
                                WHERE `co`.`author` = 65
                                OR `co`.`manager_id` = 65
                                
                            )
                            ) AS `source` JOIN `comments` AS `a` USING (`module`, `item_id`, `item_type`) 
            LEFT JOIN `comments_seen` AS `cs` ON `cs`.`comment_id` = `a`.`id` AND `cs`.`user_id` = 65 
        WHERE ((TRUE))

Execution time for the whole query, and it's UNIONed parts:

complete - 8s 451ms

1 - 775ms
2 - 1s 214ms
3 - 3s 623ms

2+3 - 4s 891ms
1+3 - 4s 314ms
1+2 - 1s 945ms

Clearly, there's only single JOIN by both comment_id and user_id columns, so the issue seem to be not in the comments_seen table as I initially thought, but rather in overall poor table design.

8
  • 1
    Surely a comment is either seen or unseen - then why have an unseen table? Just INSERT a record when the comment becomes seen? If it's not present in the table for a given user, then it must be unseen - surely? And why are you not running a more recent version of MySQL? There's a shedload of new features in recent versions that make a dev's life way easier... – Vérace Mar 31 at 19:33
  • 1
    Rename your user_id constraint to comment_seen_u_idc_id_pk and make it your PRIMARY KEY so that you know what you're looking at! How long is it taking to do a search on this table? Maybe with a UNIQUE index on comment_id_user_id might help speed up some searches? 35M records is not a hugely large table... – Vérace Mar 31 at 19:45
  • 1
    I'm not sure a comments_unseen table will have any less rows (I'd actually expect the cartesian product of unseen comments x users to be a lot higher) unless the definition of an unseen comment in this context is one that is completely new / no user has seen it yet. In any case, searching 36 million rows in a table of only two integer fields should actually be rather fast, assuming your predicates always filter on the user_id field. As @Vérace pointed out, 36 million rows is not that huge, and the fact it's only two integer columns is rather small of a table actually. If your slow... – J.D. Apr 1 at 0:25
  • 2
    ...queries are filtering on just the comment_id column then you might see much better performance creating a separate index on just comment_id so that it's covered. Curious what your search queries currently look like and the average runtimes you're experiencing, as I'd expect the search part of your query's operations should take no more than a second or so itself. (Depending on how much data you're actually returning back to the client and that runtime is a different story though, e.g. if you were returning millions of rows to the client every time.) – J.D. Apr 1 at 0:27
  • 1
    Do take a shot in upgrading to 5.6; 5.5 is past End-Of-Life; 5.6 is on the verge of such. Then come 5.7 and 5.8 to catch up. The farther behind you are, the harder it is to catch up. – Rick James Apr 1 at 18:34
1

(Addressing first question)

Toss comment_unseen; instead, use a LEFT JOIN ... WHERE ... IS NULL or WHERE NOT EXISTS ( SELECT 1 FROM comment_seen WHERE ... )

For comment_seen, replace

CONSTRAINT user_id UNIQUE (user_id, comment_id)

with this (Note: a PK includes a uniqueness constraint):

PRIMARY KEY(user_id, comment_id)

If you need to look up which users have seen a comment, add

INDEX(comment_id, user_id)

There is no need to also make that UNIQUE; in fact, there is some harm.

6
  • Doesn't PRIMARY KEY imply INDEX alongside with UNIQUE constraint? – Eduard Sukharev Apr 2 at 11:27
  • 1
    @EduardSukharev - Yes. PK is a unique, which is an INDEX. FK also implies an index (on one side). But, note that my INDEX has the columns in a different order; this is important for some queries. Also, every table needs a PK. – Rick James Apr 2 at 17:20
  • Your LEFT JOIN WHERE IS NULL and WHERE NOT EXISTS trick sadly didn't help — returned data was way off – Eduard Sukharev Apr 4 at 23:31
  • I thought every secondary index includes PK which makes the index you advised to create looks somewhat similar to (comment_id, user_id, PK(user_id, comment_id) ). Does it make sense to make it that complex? – Eduard Sukharev Apr 4 at 23:40
  • 1
    @EduardSukharev - Since those two columns are already in the secondary index, nothing need be added. The goal is for the leaf node of the secondary index's BTree to have all the columns of the PK, thereby letting it drill down the PK to find the rest of the columns in the data BTree. – Rick James Apr 5 at 5:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.