Optimization case of using forced Index in mysqlselect subquery _ MySQL

1. could you tell me the slow SQL statements SELECTpgproduct_goods_id, pgproduct_id, pgpdt_code, pgpdt_name, pgbrand_name, pgreference_price, 1. a friend asked me for help reading slow SQL statements.

SELECT    pg.product_goods_id,    pg.product_id,    pg.pdt_code,    pg.pdt_name,    pg.brand_name,    pg.reference_price,    pg.deposit,    pg.sale_status,    pg.is_delete,    pg.create_date,    pg.create_operator,    pg.update_date,    pg.update_operator,    si.shop_id,    si.shop_name,    pg.goods_img_url,    pg.is_bargain,    pg.qr_code_url,    (        SELECT            COUNT(*)        FROM            product_attention pa        WHERE            pa.product_goods_id = pg.product_goods_id        AND `status` = 0    ) AS laud,    pc.category_name,    pg.is_experience,    pg.deposit,    pg.buy_type,    pg.content,    pg.assure_flag,    pg.market_price,    pg.qty_cnt,    pg.sales_cntFROM    product_goods pg LEFT JOIN shop_info si ON si.shop_id = pg.shop_idLEFT JOIN product_category pc ON pc.category_id = pg.category_idWHERE    si.market_id IN (1, 2, 3, 12, 13)  ORDER BY pg.update_date DESC , pg.product_goods_id DESCLIMIT 0, 20;

Question 1: The following subquery is slow.

        (                SELECT                    COUNT(*)                FROM                    product_attention pa                WHERE                     pa.product_goods_id = pg.product_goods_id                AND `status` = 0            ) AS laud,

It is a little slow to add it here. Is there any optimization method? is there any way to ask?

Original blog address: blog.

2. I asked him to provide the explain analysis result.

C: \ Users \ Administrator \ Pictures \ 1105 \ e1.jpg

We can see that the temporary tablespace used in the PostgreSQL table also uses filesort, which is troublesome.

3. I asked him to provide the indexes of the tables involved.

、Pa.jpg

、Si.jpg

、Pg.jpg

The data volume of several tables is small, with more than product_goods and more than others. It should not be so slow.

4. think fast after removing order

I guess it may be caused by order by. After I asked him to remove order by, he said it was faster, but this order by cannot be easily removed, because this may be necessary. However, the order by field contains product_goods_id.

5. Solution: force primary key index

According to the analysis, the order by and subquery that caused the query to contain the product_goods_id field, which is the primary key of the PostgreSQL table, can be forced to use the primary key index instead of the shop_id index, I asked him to use product_goods pg force index (PRI) to force the primary key index. I think this is mainly because the subquery of this statement uses primary key association, however, when explaining, the shop_id index is used, and I suspect it was caused by the shop_id index. If the index of the shop_id field is not taken away, the primary key id takes into account the join table link and subquery.

Finally, after testing, I found that the problem was solved quickly. the expainresult is shown in OK .jpg, and there is no using temporary.