0

I've been investigating an issue with a slow query when trying to get the last 50 comments of a user. To give some context the database size is 9 TiB and the COMMENT table has about 7 billon of rows. Sometimes the query takes 20 seconds to execute and as you can imagine that causes a lot of troubles.

QUERY

SELECT t1.*
FROM comment t1 USE INDEX(post_comment_id_idx)
JOIN post_user t2 on t2.brand_id = t1.brand_id and t2.post_id = t1.post_id
WHERE t2.brand_id ='xxxxx' 
AND t2.user_type ='A'
AND t2.user_id ='123' 
ORDER BY t1.id DESC
LIMIT 50;

EXPLAIN

# id    select_type table   partitions  type    possible_keys   key key_len ref rows    filtered    Extra
1   SIMPLE  t1      ref post_comment_id_idx post_comment_id_idx 182 const   1   100.00  Using index condition; Using filesort
1   SIMPLE  t2      ref brand_user_idx,brand_post_idx   brand_user_idx  546 const,const,const   1   100.00  Using index condition; Using where

My first guess is that the issue is in the ORDER BY because when I remove it, the query takes ms.

Another approach I took is split the query in two like this: and it also shows an improvement in the performance.

WORKAROUND

SET @postIds := (
SELECT t2.post_id 
FROM post_user t2
WHERE t2.brand_id ='xxxxx' and t2.user_type ='A'
AND t2.user_id ='67456380001'
                            );
                            
SELECT t1.*
FROM comment t1 USE INDEX(post_comment_id_idx)
WHERE t1.brand_id ='xxxxx'
AND t1.post_id in (@postIds)
order by t1.id DESC
LIMIT 50;

But I would expect that the Database takes care of this optimization instead of having to split the query manually. It sounds more like a hack, and I think there should be a better way to access to these results with a single query.

QUESTION

Is there way I could improve this query? I need to find a solution and I would like to let the workaround as my last option.

MySQL Version: 5.7.12-log

STRUCTURE

CREATE TABLE `post` (
  `brand_id` varchar(60) NOT NULL,
  `id` varchar(26) NOT NULL,
  PRIMARY KEY (`brand_id`,`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
;

CREATE TABLE `post_user` (
  `id` varchar(26) NOT NULL,
  `user_id` varchar(100) NOT NULL,
  `user_type` varchar(20) NOT NULL,
  `brand_id` varchar(60) NOT NULL,
  `post_id` varchar(26) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `brand_user_idx` (`brand_id`,`user_type`,`user_id`),
  KEY `brand_post_idx` (`brand_id`,`post_id`),
  CONSTRAINT `brand_post_fk` FOREIGN KEY (`brand_id`, `post_id`) REFERENCES `post` (`brand_id`, `id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
;

CREATE TABLE `comment` (
  `id` varchar(26) NOT NULL,
  `brand_comment_log_seq` int(11) DEFAULT NULL,
  `customer_comment_log_seq` int(11) NOT NULL,
  `comment_flags` int(11) NOT NULL,
  `comment_json` mediumtext CHARACTER SET utf8mb4 NOT NULL,
  `comment_time` bigint(20) NOT NULL,
  `comment_type` int(11) NOT NULL,
  `inserted_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `issue_id` bigint(20) NOT NULL,
  `private` tinyint(1) NOT NULL,
  `brand_id` varchar(60) NOT NULL,
  `post_id` varchar(26) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `brand_post_time_seq_uk` (`brand_id`,`post_id`,`comment_time`,`brand_comment_log_seq`),
  KEY `post_issue_idx` (`brand_id`,`post_id`,`issue_id`),
  KEY `post_comment_id_idx` (`brand_id`,`post_id`,`id`),
  KEY `brand_comment_id_idx` (`brand_id`,`id`),
  CONSTRAINT `_FKbrand-postid` FOREIGN KEY (`brand_id`, `post_id`) REFERENCES `post` (`brand_id`, `id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
;

EDIT

EXPLAIN without USE INDEX(post_comment_id_idx)

# id, select_type, table, partitions, type, possible_keys, key, key_len, ref, rows, filtered, Extra
1, SIMPLE, t1, , ref, brand_post_time_seq_uk,post_issue_idx,post_comment_id_idx,brand_comment_id_idx, brand_post_time_seq_uk, 182, const, 1, 100.00, Using index condition; Using filesort
1, SIMPLE, t2, , ref, brand_user_idx,brand_post_idx, brand_user_idx, 546, const,const,const, 1, 100.00, Using index condition; Using where

Please, let me know if more information is needed. Thanks in advance!

3
  • Let me post the result here. ``` # id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 ref brand_post_time_seq_uk,post_issue_idx,post_comment_id_idx,brand_comment_id_idx brand_post_time_seq_uk 182 const 1 100.00 Using index condition; Using filesort 1 SIMPLE t2 ref brand_user_idx,brand_post_idx brand_user_idx 546 const,const,const 1 100.00 Using index condition; Using where ``` It's different. but it's still using filesort Commented Oct 18, 2021 at 20:15
  • Now - why are most of the ID columns varchar? are they not numeric? And why are you telling it to use post_comment_id_idx ?
    – jkavalik
    Commented Oct 18, 2021 at 20:27
  • Most of the ID columns are generated at the App level, for example for the COMMENT.ID we use ULID, that's why we decided to sort by ID. Regarding post_comment_id_idx I'm not sure why it was done that way. I assume becuse that INDEX contains the 3 main fields we are using in the query brand_id,post_id,id Commented Oct 19, 2021 at 12:58

1 Answer 1

2

Add these composite indexes:

t2 (post_user):  INDEX(brand_id, user_type, user_id,  post_id) -- replacing brand_user_idx
t1 (comment):  INDEX(brand_id, post_id, id)

(and remove the USE INDEX)

The problem is that it must gather all the possible rows based on the WHERE and ON, then sort them, and finally peel off 50. There is no shortcut to get avoid the sort and get to the limit.

Shouldn't you be ordering by comment_time instead of id?

SET @ids := ( SELECT ... ) cannot handle an array, only a single value.

There is some hope. The SELECT * needs to be done for all those rows around awaiting the order by and limit. (This may explain why the query occasionally takes longer.) Let's cut it back to a max of 50.

SELECT  t3.*
    FROM  
        ( SELECT  c1.id
            FROM  post_user pu
            JOIN  comment c1
               ON  pu.brand_id = c1.brand_id
              AND  pu.post_id = c1.post_id
            WHERE  pu.brand_id ='xxxxx'
              AND  pu.user_type ='A'
              AND  pu.user_id ='123'
            ORDER BY  c1.id DESC
            LIMIT  50 
        ) AS ids
    JOIN  comment AS c3  ON c3.id = ids.id
    ORDER BY  t3.comment_time DESC 

This way, 50 ids are fetched from an index-only ("covering" or "Using index") query, then it reaches into comment again to get the rest of the columns, a max of 50 times.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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