Запрос с оператором или, делающий выполнение запроса медленным

Я новичок и у меня проблемы с операционным запросом!

Когда я делаю это без ИЛИ, время выполнения: 0,02 секунды.

SELECT DISTINCT p.*,
                ht.html_title,
                ht.html_content,
                sc.sub_cat_name,
                sc.main_cat_id,
                us.username,
                c.name AS cityname
FROM   ad_product p,
       ad_html ht,
       ad_catagory_sub sc,
       ad_user us,
       ad_cities c
WHERE  ( ht.ad_id = p.id )
       AND ( sc.sub_cat_id = p.category )
       AND ( us.id = p.user_id )
       AND ( p.category = 216 )
       AND ( p.status = 'active' )
       AND ( c.id = p.city )
       AND ( p.city = 135 )
ORDER  BY p.created_at DESC;

И когда я пробую это с условием ИЛИ, это занимает около 4,70 секунды.

SELECT DISTINCT p.*,
                ht.html_title,
                ht.html_content,
                sc.sub_cat_name,
                sc.main_cat_id,
                us.username,
                c.name AS cityname
FROM   ad_product p,
       ad_html ht,
       ad_catagory_sub sc,
       ad_user us,
       ad_cities c
WHERE  ( ht.ad_id = p.id )
       AND ( sc.sub_cat_id = p.category )
       AND ( us.id = p.user_id )
       AND ( p.category = 216
              OR p.parent_category = 216 )
       AND ( p.status = 'active' )
       AND ( c.id = p.city )
       AND ( p.city = 135 )
ORDER  BY p.created_at DESC;

ОБЪЯСНЯТЬ:

+----+-------------+-------+------------+-------------+--------------------------------------------------------------------------------+--------------------------+---------+---------------------------+-------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type        | possible_keys                                                                  | key                      | key_len | ref                       | rows  | filtered | Extra                                              |
+----+-------------+-------+------------+-------------+--------------------------------------------------------------------------------+--------------------------+---------+---------------------------+-------+----------+----------------------------------------------------+
|  1 | SIMPLE      | c     | NULL       | const       | PRIMARY                                                                        | PRIMARY                  | 4       | const                     |     1 |   100.00 | Using temporary; Using filesort                    |
|  1 | SIMPLE      | p     | NULL       | index_merge | PRIMARY,id,parent_category,city,user_id_2,category,status,id_2,id_3,category_2 | category,parent_category | 4,4     | NULL                      | 67889 |     4.74 | Using union(category,parent_category); Using where |
|  1 | SIMPLE      | sc    | NULL       | eq_ref      | PRIMARY                                                                        | PRIMARY                  | 4       | abc_classified.p.category |     1 |   100.00 | NULL                                               |
|  1 | SIMPLE      | us    | NULL       | eq_ref      | PRIMARY                                                                        | PRIMARY                  | 4       | abc_classified.p.user_id  |     1 |   100.00 | NULL                                               |
|  1 | SIMPLE      | ht    | NULL       | ref         | ad_id                                                                          | ad_id                    | 4       | abc_classified.p.id       |     1 |   100.00 | NULL                                               |
+----+-------------+-------+------------+-------------+--------------------------------------------------------------------------------+--------------------------+---------+---------------------------+-------+----------+----------------------------------------------------+

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

Пожалуйста, отформатируйте свои запросы на несколько строк, чтобы мы могли их прочитать.

Tim Biegeleisen 31.10.2018 13:21

решение union должно работать, и, кстати, вы можете вызвать обе таблицы p, нет необходимости менять верхний запрос на p1.

isaace 31.10.2018 14:36
1
2
88
3
Перейти к ответу Данный вопрос помечен как решенный

Ответы 3

Когда вы определяете точные значения в условиях, SQL использует hash join. Так что это намного быстрее, чем другие соединения. Когда вы меняете его на условие неточного значения (X ИЛИ Y), он становится медленным.

Решение состоит в том, чтобы сделать index, чтобы уменьшить количество записей для поиска.

Я уже определил индекс как для "категории", так и для родительской_категории по отдельности. Пожалуйста, взгляните на опубликованный мной результат EXPLAIN. Он показывает, что запрос с «ИЛИ» уже использует index.

Abhinav 31.10.2018 13:54

Я думаю, что ваш запрос выиграет от объединения, а не от или. Это создает 2 набора данных. Один для categoryid, а другой - для родительского categoryid. Это будет быстрее, чем определение двух отрицательных множеств. Один набор без categoryid = 216 и один без родительского categoryid = 216.

SELECT DISTINCT p1.*,
        ht.html_title,
        ht.html_content,
        sc.sub_cat_name,
        sc.main_cat_id,
        us.username,
        c.name AS cityname
FROM   ad_product p1 inner join ad_html ht on ht.ad_id = p1.id
    inner join ad_category_sub sc on sc.sub_cat_id = p1.category
    inner join ad_user us on  us.id = p1.user_id 
    inner join ad_cities c on c.id = p1.city   
WHERE ( p1.status = 'active' )
   AND ( p1.city = 135 )
   AND ( p1.category = 216)
UNION
SELECT DISTINCT p.*,
        ht.html_title,
        ht.html_content,
        sc.sub_cat_name,
        sc.main_cat_id,
        us.username,
        c.name AS cityname
FROM   ad_product p 
    inner join ad_html ht on ht.ad_id = p.id
    inner join ad_category_sub sc on sc.sub_cat_id = p.category
    inner join ad_user us on us.id = p.user_id
    inner join ad_cities c on c.id = p.city
WHERE ( p.status = 'active' )
  AND ( p.city = 135 )
  AND (p.parent_category = 216)
ORDER  BY p.created_at DESC;

Рейнольдсон Спасибо за ответ. Я попытался выполнить запрос и получил ошибку: «# 1250 - Таблица 'p' из одного из SELECT не может быть использована в списке полей» Я не понимаю, что там написано, может быть, вы можете помочь!

Abhinav 31.10.2018 13:48

Привет, Арав, я думаю, что ошибка вызвана тем же псевдонимом «p», который используется в верхней и нижней половине запроса. Я отредактировал запрос в примере кода так, чтобы "p1" использовалось в верхней половине. Попробуй и дай мне знать, как у тебя дела. Если по-прежнему не будет радости, мне нужно будет создать мини-версию ваших структур данных для проверки запроса.

Dwight Reynoldson 31.10.2018 14:01

Привет, Рейнольдсон, спасибо за редактирование, но он дает ту же ошибку «# 1250 - Таблица 'p' из одного из SELECT не может использоваться в списке полей». Я даже попытался изменить p на p2, но не повезло.

Abhinav 31.10.2018 14:13

Привет, Арав, я снова отредактировал блок кода. Для меня это не ошибка (я работаю с тестовой структурой данных, и она написана на SQL-сервере, поэтому может потребоваться перевод на mysql, если вы используете mysql). По сути, я заменил большинство предложений where на соединения (это то, что оптимизатор сделал бы в любом случае), и я все еще использую объединение, чтобы избежать проблемы с оператором или. Надеюсь, это приблизит вас.

Dwight Reynoldson 31.10.2018 16:29

Привет, Рейнольдсон, Спасибо за решение с оптимизацией. Он работал, но без «ORDER BY», так как выдавал ту же ошибку «# 1250». После некоторого поиска я получил bugs.mysql.com/bug.php?id=68529, который показывает, что есть некоторая ошибка, когда мы используем ORDER BY с UNION или GROUP BY. Я все еще ищу решение с помощью ORDER BY, но этого достаточно. Спасибо большое за вашу помощь!

Abhinav 01.11.2018 13:00
Ответ принят как подходящий

Ответы @Dwight Reynoldson отлично работают на SQL Server, и у меня есть обходной путь для проблемы "ORDER BY" в MySQL. Все, что я сделал, это ORDER BY created_at DESC вместо ORDER BY p.created_at DESC. Это был p, который создавал всю проблему.

Отвечать:

SELECT DISTINCT p1.*,
        ht.html_title,
        ht.html_content,
        sc.sub_cat_name,
        sc.main_cat_id,
        us.username,
        c.name AS cityname
FROM   ad_product p1 inner join ad_html ht on ht.ad_id = p1.id
    inner join ad_category_sub sc on sc.sub_cat_id = p1.category
    inner join ad_user us on  us.id = p1.user_id 
    inner join ad_cities c on c.id = p1.city   
WHERE ( p1.status = 'active' )
   AND ( p1.city = 135 )
   AND ( p1.category = 216)
UNION
SELECT DISTINCT p.*,
        ht.html_title,
        ht.html_content,
        sc.sub_cat_name,
        sc.main_cat_id,
        us.username,
        c.name AS cityname
FROM   ad_product p 
    inner join ad_html ht on ht.ad_id = p.id
    inner join ad_category_sub sc on sc.sub_cat_id = p.category
    inner join ad_user us on us.id = p.user_id
    inner join ad_cities c on c.id = p.city
WHERE ( p.status = 'active' )
  AND ( p.city = 135 )
  AND (p.parent_category = 216)
ORDER BY created_at DESC;

Другие вопросы по теме