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

Question

[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 я слабо разбираюсь, поэтому буду безумно рад развёрнутому ответу, а не просто решению проблемы :)

Comments

[codercat]

tasks_done и tasks_hide объединить в одну таблицу не могу, так как там есть некоторые различия, которые в примере просто не показал(нет смысла).

[codercat]

Список заданий один для всех, но каждый юзер может скрыть задание именно для одного себя, поэтому используются отдельные таблицы tasks_done и tasks_hide.

[FanatPHP]

Рекомендую или задавать вопрос толком, или не задавать вообще. "Это я скрыл, это забыл, а это не показываю потому что неважно, но вы мне дайте точный ответ, как ускорить" - так дела не делаются. В текущем виде вопрос вообще не имеет смысла, поскольку смысла не имеют таблицы tasks_done и tasks_hide.

[codercat]

У меня там time, user_id_vk и много такой информации, которая не важна при запросе и лишь добавить лишние строки в примеры.

Смысл они имеют, ибо, повторюсь, список заданий для всех юзеров один, при этом каждый юзер может выполнить/скрыть задание ИМЕННО для себя, а не для всех юзеров вообще.

Answer 1

[FanatPHP]

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

Comments

[codercat]

Убрал вообще price и, знаете, 0.0028 сек. (SQL_NO_CACHE, да), по сравнению с 2.0973 сек при сортировке по цене. Я всё же думал, что проблема кроется именно в NOT IN, но нет ;)

[FanatPHP]

Интересно всё-таки посмотреть, как будет работать с таким индексом. Скорость должна упасть, но не так фатально, как раньше

[Дмитрий Горячев]

@codercat подскажи время выполнения запроса select ` tasks.id`, `name`, `status`, `price` From tasks left join (tasks_hide, tasks_done) on ( tasks.id = tasks_hide.task_id and tasks.id = tasks_done.task_id) where tasks_hide.user_id<>5 and tasks_done.user_id<>5 and `status` =1 LIMIT 0 , 100

[codercat]

@FanatPHP http://i.imgur.com/1AphOiu.png Запрос занял 0.0024 сек; 0.0066 сек; 0.0024 сек; 0.0754 сек; 0.0024 сек; Больше не знаю способов, как ещё можно показать результат.

[codercat]

@Gordim возможно я ошибаюсь, замеряя скорость через то, что мне отдаёт myadmin (а-ля "Отображает строки 0 - 29 ( 100 всего, Запрос занял 0.0017 сек.)"), но вот: 0.0013 сек.; 0.0016 сек.; 0.0013 сек.; 0.0013 сек.; 0.0017 сек.

[Дмитрий Горячев]

@codercat Интересует вопрос по структуре. Сколько по времени могут жить задачи в базе? Возможно имеет смысл добавить поле DATA c временем создания задачи И уже в запросе фильтровать их еще по дате. Например не брать задачи которые старше 2 недель. Что нам это даст. 1) Отсекаются все остальные записи (вероятно можно будет использовать ORDER BY `price` DESC ) 2) Если будет сильно разрастаться таблица её можно будет сегментировать по дням или месяцам.

[codercat]

Задания, как и информация о юзерах которые её выполнили, должна хранится всё время. Вообще, у задач есть счётчик, при достижении которого статус задания изменяется на 2 (done). То-есть задание выполнило 100 человек из 100 нужных — задание выполнено, статус у него становится 2. Там много данных у заданий, в том числе и time(timestamp), и time_done. С этим проблем возникнуть не должно, я думаю.

[codercat]

@Gordim забыл упомянуть

Answer 2

[Владимир Болиев]

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

Comments

[codercat]

Нет, тут список заданий один для всех и каждый юзер может выполнить/скрыть задание, после чего ему его не нужно показывать. А насчёт статуса - сейчас так и есть, в принципе (те случаи, когда задание уже не нужно показывать вообще никому)

[Владимир Болиев]

Добавьте индексы <code> ALTER TABLE `tasks_hide` ADD INDEX ( `task_id` ) ALTER TABLE `tasks_hide` ADD INDEX ( `user_id` ) ALTER TABLE `tasks_done` ADD INDEX ( `task_id` ) ALTER TABLE `tasks_done` ADD INDEX ( `user_id` ) </code>

[codercat]

@voff добавил, разницы нет

Answer 3

[Степан]

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

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

Comments

[codercat]

Я же ответил voff, что это решение по подходит. >Нет, тут список заданий один для всех и каждый юзер может выполнить/скрыть задание, после чего ему его не нужно показывать. А насчёт статуса - сейчас так и есть, в принципе (те случаи, когда задание уже не нужно показывать вообще никому)

[Степан]

тогда делайте запрос в обратном порядке. Основной селект делайте с таблиц `tasks_hide` и `tasks_done` по userid, а потом ко всему полученому подтяните id, name, status, price ... FROM `tasks` через джоин.

[codercat]

Нет, мне же не нужно получить только скрытые/выполненные.

Answer 4

[Дмитрий Горячев]

Можешь показать что показывает explain?
Какая версия mysql?
покажи my.cnf

Comments

[Степан]

Извините, каким боком тут версия mysql и my.<b>cnf</b>?

[Дмитрий Горячев]

Спасибо поправил!

[codercat]

Только если так - http://i.imgur.com/0uZe9rK.png Версия (select version();) - 5.5.31-0ubuntu0.12.10.1 Там я ничего не трогал - http://pastebin.com/Bg7CTxDj

[Дмитрий Горячев]

@codercat Посмотри как будет выполняться такой запрос select ` tasks.id`, `name`, `status`, `price` From tasks left join (tasks_hide, tasks_done) on ( tasks.id = tasks_hide.task_id and tasks.id = tasks_done.task_id) where tasks_hide.user_id<>5 and tasks_done.user_id<>5 and `status` =1 ORDER BY `price` DESC LIMIT 0 , 100

[Дмитрий Горячев]

Еще нужно будет добавить один индекс ALTER TABLE `test`.`tasks` ADD INDEX `status` (`status` ASC);

[codercat]

@Gordim ~Запрос занял 3.9167 сек.; http://i.imgur.com/P6wUbX3.png

[codercat]

@Gordim но, опять же, зачем вообще объединять таблицы? Мне наоборот нужно получить то, чего нет в tasks_done и tasks_hide

Answer 5

[Кирилл Фирсов]

Попробуйте добавить индекс

ALTER table tasks ADD index test_index (`id`, `status`, `price`, `name`);

и снова покажите EXPLAIN.

Comments

[codercat]

Сомневаюсь, что тут проблема в этом, но вот http://i.imgur.com/j9a2h7e.png

[Кирилл Фирсов]

@codercat ну зато теперь индекс использует. Осталось подзапросы на джоины перевести.

[codercat]

@Isis а зачем мне объединять таблицы? Или Вы предлагаете сделать что-то вроде SELECT `id`, `name`, `status`, `price` FROM `tasks` LEFT OUTER JOIN tasks_hide ON tasks_hide.task_id = tasks.id AND tasks_hide.user_id = 5 WHERE tasks_hide.task_id IS NULL .........

Answer 6

[Петруша Укропов]

Объедените таблицы 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

Comments

[codercat]

Но тогда придётся поддерживать сразу 2-3 таблицы, так как в tasks_done у меня много нужной информации хранится и я просто не могу избавиться от неё. Так же я не уверен, что скорость очень сильно уменьшится, если там будет один NOT IN, а не два.

[Петруша Укропов]

Если таблицы объединить перенеся все данные в одну таблицу, то старые поддерживать не надо будет.

[codercat]

@artishok не, решение уже найдено + там много разной информации, которую не хотелось бы объединять в одну сущность.