Optimize query to not read all rows in table

Question

I have this in my slow query log.

Slow Query Log

# Time: 2020年03月09日T08: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 ...

• dbdemon: Which version of MySQL is this?
• NikitaSerbskiy: could you please remove distinct and add output of explain command?
• mustaccio: Similar
• Joe W: you are doing a select distinct * on a table so why would it not read the entire table?
• NikitaSerbskiy: @mustaccio this case is more complex because query contains conditions on joined table

Answer 1

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

Answer 2

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