Loveorigami писал(а): ↑2017.02.12, 11:47
индекс на user_id и status проставлен?
Поиск у такого запроса идет только по user_id, поэтому индекс нужен только по этому полю.
А вообще индексы по статусным полям (уникальных значений у которых мало) бесполезны, ни один движок СУБД не будет использовать индекс, если скажем более 20% записей в таблице имеет одно значение статуса.
Какой средний процент строчек, где статус уже равен 1 для user_id?
--------------------------------------------------------------------------------------
Провел небольшие тесты с разными вариантами индексов и запросами (MyISAM, 0,5 млн записей, 70 Мб):
=================================
НИ ОДНОЙ ЗАПИСИ С НУЖНЫМ СТАТУСОМ
=================================
Только проверяем раздел (update 'table' set active='1' where cat='25')
-----------------------
1. Индекс только на раздел = Затронуто 36469 строк. (Запрос занял 0.1580 сек.)
2. Индекс только на статус = Затронуто 36469 строк. (Запрос занял 0.7081 сек.)
3. Индекс раздел + статус = Затронуто 36469 строк. (Запрос занял 0.2723 сек.)
4. Индекс раздел, индекс статус = Затронуто 36469 строк. (Запрос занял 0.2794 сек.)
Проверяем раздел и статус (update 'table' set active='1' where cat='25' and active!='1')
-------------------------
1. Индекс только на раздел = Затронуто 36469 строк. (Запрос занял 0.1812 сек.)
2. Индекс только на статус = Затронуто 36469 строк. (Запрос занял 0.7331 сек.)
3. Индекс раздел + статус = Затронуто 36469 строк. (Запрос занял 0.2813 сек.)
4. Индекс раздел, индекс статус = Затронуто 36469 строк. (Запрос занял 0.2807 сек.)
==========================
==================================
ПОЛОВИНА ЗАПИСЕЙ С НУЖНЫМ СТАТУСОМ
==================================
Только проверяем раздел (update 'table' set active='1' where cat='25')
-----------------------
1. Индекс только на раздел = Затронуто 18230 строк. (Запрос занял 0.1102 сек.)
2. Индекс только на статус = Затронуто 18230 строк. (Запрос занял 0.5936 сек.)
3. Индекс раздел + статус = Затронуто 18230 строк. (Запрос занял 0.1679 сек.)
4. Индекс раздел, индекс статус = Затронуто 18230 строк. (Запрос занял 0.1742 сек.)
Проверяем раздел и статус (update 'table' set active='1' where cat='25' and active!='1')
-------------------------
1. Индекс только на раздел = Затронуто 18230 строк. (Запрос занял 0.1092 сек.)
2. Индекс только на статус = Затронуто 18230 строк. (Запрос занял 0.6315 сек.)
3. Индекс раздел + статус = Затронуто 18230 строк. (Запрос занял 0.1431 сек.)
4. Индекс раздел, индекс статус = Затронуто 18230 строк. (Запрос занял 0.1691 сек.)
==========================
Первые выводы:
1. Сделайте индекс по user_id;
2. В запросах проверять статус не нужно, похоже, это делает БД в движке сама.
Но лучше потестите у себя, ведь много зависит от движка БД, типа полей и т.д.
ТС, если объем таблицы и дальше будет расти, то я бы предложил посмотреть в следующие стороны:
1) посмотреть можно ли добавить еще поле актуальности, например дату с индексом (мб даже кластерным) и обрабатывать только актуальные (свежие) записи. В таком случае сперва будет отбор множества только актуальных записей по дате, а потом фильтр по user_id;
2) проанализировать существующие индексы в таблице, мб есть ненужные и удалить их. Индексы ускоряют селект но немного замедляют update и delete;
3) Если таблица с большим количеством колонок и среди них много текстовых (по ним редко работают индексы и отбор), то можно такие большие текстовые поля вынести в отдельные таблицы со связью по первичному ключу "один к одному". Такая оптимизация помогает, так как движку СУБД приходится перемещаться с шагом по длине 1 строки таблицы (чем короче строка, тем быстрее обход таблицы). Мной такое тестировалось на таблице в postgresql при количестве строк около 600 млн.
p.s. задумался в какой ситуации нужен такой запрос, может быть вы просто хотите ускорить эту обработку при вызове из браузера? В таком случае может вам перейти к cron обработчику, который будет делать этот update в фоном режиме?
там замута в том, что в конкретно в нужной таблице используются 13 индексов для ускорения выборки, отвечу сразу: да, они все нужные. Скорее всего дело в них, но если я их вырежу то выборка не будет такой оперативной, как это необходимо. На user_id индекс также стоит