@codercat

Как оптимизировать запрос?

Делаю проект, в котором можно выполнять/скрывать задания.

Всё работало шустро, но позже я решил добавить 300 000 записей в таблицу заданий и по 100 000 в таблицы tasks_done и tasks_hide.
Результат: время выполнения - 1.2 сек (Если добавить SQL_NO_CACHE, с ним +-), что не очень хорошо.

Для получения заданий использую вот такой запрос:

SELECT id, name, status, price ...
FROM  `tasks` 
WHERE  `tasks`.id NOT IN (
 SELECT  `task_id` 
 FROM  `tasks_done` 
 WHERE  `user_id` = 5
)
AND  `tasks`.id NOT IN (
 SELECT  `task_id` 
 FROM  `tasks_hide` 
 WHERE  `user_id` = 5
)
AND  `status` =1
ORDER BY  `price` DESC 
LIMIT 0 , 100


Примерные структуры таблиц:
CREATE TABLE IF NOT EXISTS `tasks` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `status` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  `price` int(11) NOT NULL
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=315426;

CREATE TABLE IF NOT EXISTS `tasks_done` (
  `task_id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL
  UNIQUE KEY `task_id_user_id` (`task_id`,`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `tasks_hide` (
  `task_id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  UNIQUE KEY `task_id_user_id` (`task_id`,`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


В mysql я слабо разбираюсь, поэтому буду безумно рад развёрнутому ответу, а не просто решению проблемы :)

  • Вопрос задан
  • 2803 просмотра
Решения вопроса 1
FanatPHP
@FanatPHP
Помогаю и новичкам, и старичкам
Посмотрел на запрос повнимательнее.
Сдается мне, тут основная проблема в сортировке основной таблицы по неиндексированному полю.
Предлагаю для начала сделать индекс по полю прайс.
ALTER TABLE tasks ADD INDEX (status,price);
причем, поскольку нужна обратная сортировка, а mysql ее в индексах не поддерживает, то цену хранить в отрицательных величинах, а при извлечении брать её по модулю. Пока же для тестов просто убрать DESC из запроса.
Ответ написан
Пригласить эксперта
Ответы на вопрос 5

Добавьте в таблицу tasks еще один статус (один у вас есть, но непонятно зачем он нужен). Пусть это будет поле status1, имеющее значение 0 - если задание не скрыто и не выполненно, 1 - если скрыто, 2 - если выполненно. Теперь можно брать не скрытые и не выполненные задания просто по статусу, отбросив сразу два подзапроса.

Ответ написан

Вы explain запроса делали?
Использовать просто IN не очень то рекомендуют, а у вас вообще IN (SELECT ...)
Сделайте explain вашего запроса и смотрите сколько затронуто строк. В вашем случае это будет несколько млн сортировок.

Делайте как вам рекомендуют выше. Добавьте поле в tasks и делайте его индексом. Поле делайте tinyint или set('done','hide') что бы не занимало много места.

Ответ написан
Можешь показать что показывает explain?
Какая версия mysql?
покажи my.cnf
Ответ написан
Попробуйте добавить индекс
ALTER table tasks ADD index test_index (`id`, `status`, `price`, `name`);

и снова покажите EXPLAIN.
Ответ написан
@artishok
кратко
Объедените таблицы done и hide в одну, например tasks_status

CREATE TABLE IF NOT EXISTS `tasks_status` (
  `task_id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
`status` int(1) NOT NULL,
  UNIQUE KEY `task_id_user_id` (`task_id`,`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Где и будет храниться статус задания, как писал voff, статусы - 0,1,2
Ответ написан
Ваш ответ на вопрос

Войдите, чтобы написать ответ

Войти через центр авторизации
Похожие вопросы