Answer the question
In order to leave comments, you need to log in
Why does MySQL only use part of the index?
There is a table that helps to process jobs (more than 300 million records):
CREATE TABLE `test` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`job_id` int(10) unsigned NOT NULL,
`lock` mediumint(6) unsigned DEFAULT '0',
`time` timestamp NULL DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `job_id` (`job_id`),
KEY `lock` (`lock`),
KEY `time` (`time`),
KEY `lock_time` (`lock`,`time`)
) ENGINE=MyISAM;
UPDATE `test` SET `lock` = 123456 WHERE `lock` = 0 ORDER BY `time` LIMIT 1000
SELECT * FROM `test` WHERE `lock` = 123456
UPDATE `test` SET `lock` = 1 WHERE `lock` = 123456
SELECT SQL_NO_CACHE * FROM `test` WHERE `lock` = 0 ORDER BY `time` LIMIT 1000
UPDATE `test` SET `lock` = 123456 WHERE `lock` = 0 ORDER BY `time` LIMIT 1000
Answer the question
In order to leave comments, you need to log in
Everything is very simple:
1000 rows affected. (The request took 0.0736 seconds)
Thank you all!
But a similar UPDATE query takes an unacceptably long time (286.0228 sec.)
UPDATE `test` SET `lock` = 123456 WHERE `lock` = 0 ORDER BY `time` LIMIT 1000
Maybe because there is an index on lock and this time is obtained due to his rearrangement?
I re-read it four times - I still don’t understand why you need time when you go to the field with the process ID to zero. If you first do this:
then why not then do it exactly to the set?
If you say that several processes can execute a task, and you can't just put zeros on all tasks that have 1, then I will tell you that you need a normal table structure, and you should separate "lock" and "process_id". Although of course it is not clear why you need such a transition: 0 -> process_id -> 1 -> 0, do 0 -> process_id -> 0, and you will have normal requests:
UPDATE `test` SET `process_id` = 123456 WHERE `process_id` = 0 ORDER BY `time` LIMIT 1000
UPDATE `test` SET `process_id` = 0 WHERE `process_id` = 123456
Here you can do this:
SET @id = 0;
UPDATE `test` SET `lock` = 1 WHERE @id := `id` AND `lock` = 0 ORDER BY `time` LIMIT 1;
SELECT @id;
Have you tried Order by lock asc, time asc? The mysql scheduler doesn't always work well, perhaps one of those cases.
Didn't find what you were looking for?
Ask your questionAsk a Question
731 491 924 answers to any question