Solving SQL problems will definitely take your understanding of MySQL one step further!

SQL Tutorial This column introduces how to understand MySQL more effectively

##Recommended (free): SQL tutorial

The attribute table (product_props) structure is as follows

Data volume is more than 800W

Field nameTypeDescriptionidintidpn_idintAttribute typepv_idintAttribute valueproduct_idintProduct IDThere is a one-to-many relationship between product_id, pn_id, and pv_id.

The data is similar to this:

product_idpn_idpv_id109705 (Model) 135 (Apple 9) 1097011 (Memory) 23 (512G)1097010 (Color)17 (Local gold)109708 （Network）6（5G）109805135109801124 (1024G) ##10980

10	16 (Aurora Blue)

The product table (product) structure is as follows

data Amount above 40W

Field nameproduct_idtype_idbrand_idmodel_idstatusThe data is similar to the following:

Type	Description
int	product_id
int	typeid
int	brandid
int	Model id
tinyint	status

product_id109701098010981

type_id	brand_id	model_id	status
1(Mobile)	1(Apple)	1(Iphone8)	1(Normal)
1(Mobile)	1(Apple)	1(Iphone8X)	3 (Sold)
1(Mobile)	1(Apple)	1(Iphone8XP)	1(Normal)

Problem

Find out the model number is

Apple 9

At the same time, the memory is 512G, color is local gold, status is normal, the total number of products is , ps: attribute conditions may exceed 10 Group.
Requirements

Performance comes first, avoid aggregate functions, etc.

Performance ranking of solutions to the original problem

Exist scheme from @Kamicloud

1. SELECT 
       sql_no_cache `product_id`
   FROM
       `zx_tests` AS a
   WHERE
       `pn_id` = 101 AND `pv_id` = 59
           AND EXISTS( SELECT 
              sql_no_cache  *
           FROM
               `zx_tests`
           WHERE
           a.product_id = product_id and
               `pn_id` = 101 AND `pv_id` = 171);
   
       2 组条件下 0.657，3 组 0.695，4 组 0.759，5 组 0.743 （单独查属性表）

Subquery scheme from @Elijah_Wang

2. SELECT `product_id` FROM `product` WHERE `pn_id` = 5 AND `pv_id` = 135 AND `product_id` IN (SELECT `product_id` FROM `product` WHERE `pn_id` = 11 AND `pv_id` = 23);
   
       2 组条件下 0.729，3 组 0.75，4 组 0.730，5 组 0.757 (新问题之前)

Performance ranking after new questions

Subquery plan from @Elijah_Wang

1.     select SQL_NO_CACHE count(1) from pdi_product a join  (
           SELECT
                distinct product_id
           FROM
               `product_props` 
           WHERE
               `pn_id` = 5 
               AND `pv_id` = 127
               AND `product_id` IN ( SELECT  `product_id` FROM `product_props` WHERE `pn_id` = 11 AND `pv_id` = 22 ) 
               AND `product_id` IN ( SELECT  `product_id` FROM `product_props` WHERE `pn_id` = 10 AND `pv_id` = 18 ) 
               AND `product_id` IN ( SELECT  `product_id` FROM `product_props` WHERE `pn_id` = 8 AND `pv_id` = 6 )  
               AND `product_id` IN ( SELECT  `product_id` FROM `product_props` WHERE `pn_id` = 9 AND `pv_id` = 1 )
               ) b on a.product_id = b.product_id 
               where  a.status = 1；

It takes 1.5-1.56 (range of 10 executions)

expain analysis:

        select SQL_NO_CACHE count(1) from pdi_product a 
            where  a.status = 1 and a.product_id in (SELECT
             distinct product_id
        FROM
            `product_props` 
        WHERE
            `pn_id` = 5 
            AND `pv_id` = 127
            AND `product_id` IN ( SELECT  `product_id` FROM `product_props` WHERE `pn_id` = 11 AND `pv_id` = 22 ) 
            AND `product_id` IN ( SELECT  `product_id` FROM `product_props` WHERE `pn_id` = 10 AND `pv_id` = 18 ) 
            AND `product_id` IN ( SELECT  `product_id` FROM `product_props` WHERE `pn_id` = 8 AND `pv_id` = 6 )  
            AND `product_id` IN ( SELECT  `product_id` FROM `product_props` WHERE `pn_id` = 9 AND `pv_id` = 1 ))

takes 0.69-0.72 (range of execution 10 times)

explain analysis:

• 
  

exist solution from @Kamicloud

2.     SELECT SQL_NO_CACHE
               count(1) 
           FROM
               product a
           WHERE
               a.STATUS = 1 
               AND a.product_id IN (
           SELECT DISTINCT
               `product_id` 
           FROM
               `product_props` AS a 
           WHERE
               a.`pn_id` = 5 
               AND a.`pv_id` = 127 
               AND EXISTS ( SELECT product_id FROM `product_props` WHERE a.product_id = product_id AND `pn_id` = 11 AND `pv_id` = 22 ) 
               AND EXISTS ( SELECT product_id FROM `product_props` WHERE a.product_id = product_id AND `pn_id` = 10 AND `pv_id` = 18 ) 
               AND EXISTS ( SELECT product_id FROM `product_props` WHERE a.product_id = product_id AND `pn_id` = 9 AND `pv_id` = 1 ) 
               AND EXISTS ( SELECT product_id FROM `product_props` WHERE a.product_id = product_id AND `pn_id` = 8 AND `pv_id` = 6 ) 
               );

It takes 5.7-5.85 (range of execution 10 times)

explain analysis:

    SELECT SQL_NO_CACHE
        count(1) 
    FROM
        pdi_product a
        join (SELECT DISTINCT
        `product_id` 
    FROM
        `product_props` AS a 
    WHERE
        a.`pn_id` = 5 
        AND a.`pv_id` = 127 
        AND EXISTS ( SELECT product_id FROM `product_props` WHERE a.product_id = product_id AND `pn_id` = 11 AND `pv_id` = 22 ) 
        AND EXISTS ( SELECT product_id FROM `product_props` WHERE a.product_id = product_id AND `pn_id` = 10 AND `pv_id` = 18 ) 
        AND EXISTS ( SELECT product_id FROM `product_props` WHERE a.product_id = product_id AND `pn_id` = 9 AND `pv_id` = 1 ) 
        AND EXISTS ( SELECT product_id FROM `product_props` WHERE a.product_id = product_id AND `pn_id` = 8 AND `pv_id` = 6 ) ) b
    on a.product_id = b.product_id
    WHERE
        a.STATUS = 1

It takes 5.7-6.0 (range of execution 10 times)

explain analysis:

#It can be seen that if you simply check the attribute table, the speed of the first position is the fastest, but when you check the product status, the speed is not as good as the subquery.

---

After explain analysis, the reason why the first subquery is fast is because its SQL is simple and the select_type is simple.

Regardless of join or exists method, select_type is mostly DERIVED and DEPENDENT SUBQUERY.

Related free learning recommendations:

mysql video tutorial

The above is the detailed content of Solving SQL problems will definitely take your understanding of MySQL one step further!. For more information, please follow other related articles on the PHP Chinese website!