Блог

У меня есть таблица с объектами и связанными таблицами object_info, object_theme (категория), местоположения и владельцы объектов.

Что я хочу, так это сначала показать все объекты от владельцев объектов с более высоким приоритетом, а затем остальные. Итак, мой запрос выглядит примерно так:

 SELECT 
   (...)
FROM objects
INNER JOIN object_info ...
INNER JOIN objectowner_info ...
INNER JOIN locations ...
WHERE object_active = 1
  AND object_owner_active = 1
ORDER BY object_owner_priority DESC,
         object_price ASC
   LIMIT 0, 10
  

Как вы можете видеть, я выбираю все объекты и предоставляю тем из владельцев объектов со статусом более высокого приоритета преимущество показа первыми. И затем сортировка от самой низкой цены вверх. Но большую часть времени этот запрос выполняется чрезвычайно медленно.

Каковы наилучшие шаги для оптимизации этого запроса? Я перепробовал все виды индексов, но узким местом, похоже, является сортировка. Когда я убираю это, запрос выполняется в порядке с точки зрения скорости.

(Пожалуйста, обратите внимание, что я не объединял темы (категории), я думаю реализовать это по-другому из-за отношений 1: n, которые вам также понадобятся для группировки результата, и это кажется ужасно медленным. Все остальные таблицы, упомянутые в объединении, имеют соотношение 1: 1.).

Для сравнения: запрос без сортировки по обоим столбцам занимает 0,0011 секунды. Упомянутый выше с обоими столбцами 0.8779. Но в зависимости от нагрузки это может занять даже секунды.

ОБЪЯСНИТЕ с помощью сортировки:

 id  select_type     table   type    possible_keys                                       key                 key_len     ref             rows    Extra
1   SIMPLE          o       ALL     PRIMARY,fk_object_user,fk_object_city,type active   NULL                NULL        NULL            63773   Using where; Using temporary; Using filesort
1   SIMPLE          ooi     ref     fk_objectowner_id                                   fk_objectowner_id   4           o.object_user   1       Using where
1   SIMPLE          oo      eq_ref  PRIMARY,id_and_status                               PRIMARY             4           o.object_user   1       Using where
1   SIMPLE          l       eq_ref  PRIMARY                                             PRIMARY             4           o.object_city   1       Using where
1   SIMPLE          oi      ref     fk_info_lang,fk_info_object,lang_object             fk_info_object      3           o.object_id     1       Using where
  

ОБЪЯСНЯТЬ без сортировки:

 id  select_type     table   type    possible_keys                                       key                 key_len     ref             rows    Extra
1   SIMPLE          o       ALL     PRIMARY,fk_object_user,fk_object_city,type active   NULL                NULL        NULL            63773   Using where
1   SIMPLE          ooi     ref     fk_objectowner_id                                   fk_objectowner_id   4           o.object_user   1   Using where
1   SIMPLE          oo      eq_ref  PRIMARY,id_and_status                               PRIMARY             4           o.object_user   1   Using where
1   SIMPLE          l       eq_ref  PRIMARY                                             PRIMARY             4           o.object_city   1   Using where
1   SIMPLE          oi      ref     fk_info_lang,fk_info_object,lang_object             fk_info_object      3           o.object_id     1   Using where
  

Определите индексы в object_owner_priority и object_price и измените предложение where на что-то вроде:

 WHERE object_active   0 = 1
  AND object_owner_active   0 = 1
  

Если повезет, это должно сработать. Если вы определили индексы для object_active или object_owner_active, рассмотрите возможность их удаления.

Что ж, теперь проблема решена путем создания отдельных столбцов заказа и заполнения их с помощью задания cron, которое время от времени выполняет медленный запрос для генерации требуемого заказа.