0

I have this in my slow query log.

Slow Query Log

# Time: 2020-03-09T08:42:46.946084Z
# User@Host: admin_admin[admin_admin] @ localhost [127.0.0.1]  Id:    16
# Query_time: 0.001558  Lock_time: 0.000278 Rows_sent: 0  Rows_examined: 255
SET timestamp=1583743366;
select DISTINCT q.id,q.*
            from bot_message_queue q
            left join bot_message_queue_wait w1 on q.botId=w1.botId AND q.chatId=w1.chatId
            left join bot_message_queue_wait w2 on q.botId=w2.botId AND w2.chatId=0
            where
              q.status=0 AND

              (w2.retry_after IS NULL OR w2.retry_after < 1583743366) AND
              (w1.retry_after IS NULL OR w1.retry_after < 1583743366)
            order by q.priority DESC,q.id ASC
            limit 1;

My tables

Following the DDL for the tables involved in the query.

bot_message_queue

CREATE TABLE IF NOT EXISTS `bot_message_queue` (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `botId` int(10) UNSIGNED NOT NULL,
  `chatId` varchar(50) CHARACTER SET utf8 NOT NULL,
  `type` varchar(50) DEFAULT NULL,
  `message` longtext,
  `add_date` int(10) UNSIGNED NOT NULL,
  `status` tinyint(2) UNSIGNED NOT NULL DEFAULT '0' COMMENT '0=waiting,1=sendig,2=sent,3=error',
  `priority` tinyint(1) UNSIGNED NOT NULL DEFAULT '5' COMMENT '5=normal messages,<5 = bulk messages',
  `delay_after` int(10) UNSIGNED NOT NULL DEFAULT '1000',
  `send_date` int(10) UNSIGNED DEFAULT NULL,
  `identifier` varchar(50) CHARACTER SET utf8 DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `botId` (`botId`,`status`),
  KEY `chatId` (`chatId`,`status`),
  KEY `botId_2` (`botId`,`chatId`,`status`,`priority`,`identifier`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
COMMIT;

bot_message_queue_wait


CREATE TABLE IF NOT EXISTS `bot_message_queue_wait` (
  `botId` int(10) UNSIGNED NOT NULL,
  `chatId` varchar(50) CHARACTER SET utf8 NOT NULL,
  `retry_after` int(10) UNSIGNED NOT NULL,
  PRIMARY KEY (`botId`,`chatId`),
  KEY `retry_after` (`retry_after`),
  KEY `botId` (`botId`,`chatId`,`retry_after`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Constraints for dumped tables
--

--
-- Constraints for table `bot_message_queue_wait`
--
ALTER TABLE `bot_message_queue_wait`
  ADD CONSTRAINT `message_queue_wait_botId` FOREIGN KEY (`botId`) REFERENCES `bot` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;
COMMIT;

Question

It's not slow, but it is reading the whole table on every request.

What indexes should I change/add to fix this?

UPDATE

Explain result without distinct

query explain result

UPDATE 2

This is a very heavy read/write/update table, its the single most used table in my entire database, usually it has just less than 1000 rows but it could go up to 10000+ when someone is sending bulk messages ...
I clean up processed rows every hour to keep it small ...

Improve this question
9
  • Which version of MySQL is this? Commented Mar 9, 2020 at 10:04
  • could you please remove distinct and add output of explain command? Commented Mar 9, 2020 at 11:05
  • Similar Commented Mar 9, 2020 at 12:20
  • you are doing a select distinct * on a table so why would it not read the entire table? Commented Mar 9, 2020 at 12:49
  • @mustaccio this case is more complex because query contains conditions on joined table Commented Mar 9, 2020 at 13:47

2 Answers 2

Reset to default
0

Since retry_after is NOT NULL, you do not need to check for NULL. Removing that check will get rid of OR, which prevents sargability.

ORDER BY q.priority DESC, q.id ASC probably requires a sort, at least because of the two columns going in opposite directions (DESC/ASC). (With MySQL 8.0 you could have an index that splits like that.) Would it work just as well to do q.id DESC? Or maybe invert the values of q.priority so both can be ASC. Note: you would have to change all the values and the code; simply using an expression (-q.priority) would only make things worse.

The DISTINCT effective causes another sort -- to dedup. With the LIMIT 1, who cares if there are dups!

Putting the pieces together:

select  q.id
    from  bot_message_queue q
    where  q.status=0
      AND  EXISTS (
        SELECT  1
            FROM  bot_message_queue_wait w1
            WHERE  botId=w1.botId
              AND  q.chatId=w1.chatId
              AND  w1.retry_after < 1583743366
                  )
      AND  EXISTS (
        SELECT  1
            FROM  bot_message_queue_wait w2
            WHERE  botId=w2.botId
              AND  0=w2.chatId
              AND  w2.retry_after < 1583743366
                  )
    order by  q.priority DESC, q.id DESC
    limit  1;

And have these indexes for bot_message_queue, in the order given. I don't know which one is better:

(status, chatId, priority, id)
(status, priority, id, chatId)

and get rid of this on bot_message_queue_wait; it is redundant with the data plus the clustered PK:

KEY `botId` (`botId`,`chatId`,`retry_after`)

Because of the LIMIT, you should not trust the "Rows" in EXPLAIN. Instead, use the "Handler counts" technique described here: http://mysql.rjweb.org/doc.php/index_cookbook_mysql#handler_counts

Improve this answer
0

Try this:

CREATE INDEX ix_bot_message_queue_status ON bot_message_queue (status, priority desc, id, botId, chatId);           
select q.id
from bot_message_queue q
where
              q.status=0 
              AND
              (NOT EXIST (SELECT 1 FROM bot_message_queue_wait w1 WHERE botId=w1.botId AND q.chatId=w1.chatId) 
              OR EXIST (SELECT 1 FROM bot_message_queue_wait w1 WHERE botId=w1.botId AND q.chatId=w1.chatId AND w1.retry_after < 1583743366))
              AND 
              (NOT EXIST (SELECT 1 FROM bot_message_queue_wait w2 WHERE botId=w2.botId AND 0=w2.chatId) 
              OR EXIST (SELECT 1 FROM bot_message_queue_wait w2 WHERE botId=w2.botId AND 0=w2.chatId AND w2.retry_after < 1583743366))

order by q.priority DESC,q.id ASC
limit 1;

you may need to use a trick with generated column if your MySQL version doesn't support descending indexes

Improve this answer

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.