SQL查询优化-执行时间 [英] SQL Query optimization - exec time

问题描述

我遇到以下查询，该查询花了我2秒钟的时间执行，现在大约需要10分钟，因为我在表custom_redemptions中增加了100个条目，而在其他表中又增加了10个条目.

I am having the following query which took me 2 seconds to execute and now it takes ~10 minutes because I added 100 more entries in tables custom_redemptions and around 10 more in each other table.

使用DISTINCT返回我

Showing rows 0 - 29 (96 total, Query took 0.00156 sec)

我拿出DISTINCT跟踪该错误，并返回了我

I took out DISTINCT to track the bug and it returned me

 Showing rows 0 - 29 (94174080 total, Query took 0.1510 sec)

有什么方法可以对此进行优化?

Is there any way to optimize this?

SELECT DISTINCT c.id, c.couponid, c.branchid, c.chainid
FROM `users_roles` a,
     `field_data_field_ypefthinos` b,
     `custom_redemptions` c,
     `field_data_field_chain_manager` e,
     `field_data_field_node_tax_inception` f,
     `field_data_field_brand_manager` j,
     `field_data_field_brand_node_ref` k,
     `field_data_field_product_ref` i,
     `field_data_field_company_manager` z,
     `field_data_field_brand_company` t
WHERE (a.rid = 4
       AND a.uid = 351
       AND b.field_ypefthinos_uid = a.uid
       AND b.entity_id = c.branchid)
   OR
      (a.rid = 5
       AND a.uid = 351
       AND e.field_chain_manager_uid = a.uid
       AND e.entity_id = f.entity_id
       AND f.field_node_tax_inception_tid = c.chainid)
   OR
      (a.rid = 9
       AND a.uid = 351
       AND j.field_brand_manager_uid = a.uid
       AND j.entity_id = k.field_brand_node_ref_nid
       AND k.entity_id = i.field_product_ref_nid
       AND i.entity_id = c.couponid)
   OR
      (a.rid = 6
       AND a.uid = 351
       AND z.field_company_manager_uid = a.uid
       AND z.entity_id = t.field_brand_company_nid
       AND t.entity_id = k.field_brand_node_ref_nid
       AND k.entity_id = i.field_product_ref_nid
       AND i.entity_id = c.couponid)

推荐答案

步骤1.您具有4个条件的OR.将查询变成4个SELECTs的UNION，每个都有OR个条件之一.

Step 1. You have the OR of 4 conditions. Turn the query into the UNION of 4 SELECTs, each with one of the OR conditions.

步骤2.删除每个SELECT中未使用的表(以摆脱笛卡尔乘积).

Step 2. Remove the unused tables in each SELECT (to get rid of the Cartesian products).

第3步.将INDEX(uid, rid)(以任意顺序)添加到a.

Step 3. Add INDEX(uid, rid) (in either order) to a.

这篇关于SQL查询优化-执行时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！