使用或使查询执行缓慢的查询

g6baxovj 于 2021-06-18 发布在 Mysql

关注(0)|答案(3)|浏览(338)

我是一个新手，有麻烦与一个或查询！
当我做它没有或执行时间是：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                                               |
+----+-------------+-------+------------+-------------+--------------------------------------------------------------------------------+--------------------------+---------+---------------------------+-------+----------+----------------------------------------------------+

我想我已经定义了适当的索引，有人能指出我做错了什么吗？或者如何减少执行时间？我知道为什么会这样？

sql mysql performance conditional-statements

来源：https://stackoverflow.com/questions/53083204/query-with-or-making-query-execution-slow

3条答案

按热度按时间

z0qdvdin1#

在where条件中定义精确值时，sql使用 hash join . 所以它比其他连接要快得多。当您将其更改为非精确值条件（x或y）时，它会变慢。
解决办法是做一个 index 以减少要搜索的记录。

赞(0）回复(0）举报 2021-06-18

nwsw7zdq2#

我认为你的查询将受益于工会而不是or。这将创建两组数据。一个在类别ID上，另一个在父类别ID上。这比定义两个负集要快。一组没有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;

赞(0）回复(0）举报 2021-06-18

8ehkhllq3#

@dwight reynoldson answers在sql server上运行得很好，我在mysql上找到了一个“order by”问题的解决方法。我所做的只是 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;

赞(0）回复(0）举报 2021-06-18

我来回答

使用或使查询执行缓慢的查询

3条答案

相关问题

热门标签

最新问答