Optimization case of using forced index in MySQL select Neutron query

1, my friend, look for me. Slow SQL statements

  SELECT pg.product_goods_id, pg.product_id, Pg.pdt_code, Pg.pdt_name, Pg.brand_name, Pg.refere     Nce_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 = P g.product_goods_id and ' status ' = 0) as Laud, Pc.category_name, Pg.is_experience, Pg.deposit, Pg.bu Y_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.ma rket_id in (1, 2, 3,.) ORDER by Pg.update_date DESC, pg.product_goods_id desclimit 0;  

Question 1, with the following sub-query, is relatively slow

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

It is a bit slow to add here, what is the optimization method, ask what methods?

Original Blog address: http://blog.csdn.net/mchdba/article/details/49667417, without the original author's consent, declined reproduced.

2, I gave him a explain analysis of the results of the execution

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

As you can see, the use of temporary table space in the PG table is also used in the Filesort, this point is more troublesome.

3, I let him provide an index of several tables related to the situation

, pa.jpg

, si.jpg

, pg.jpg

A few tables of data volume are small, product_goods more than 6w, the other more than 3,000. Should not be so slow.

4. Think faster after removing order by

I guess it might be the order by, and I let the order by be removed, he said faster, but the order by cannot be easily removed, as this is probably needed. But there are product_goods_id in the order by field.

5. Solution: Force primary key Index

Analysis to the cause of the order by and the subquery has a product_goods_id field, and this field is the primary key of the PG table, so you can force the primary key index without going to the shop_id index, I let him use Product_goods PG force Index (PRI) enforces the use of primary key indexes, I think, mainly because the subquery of this statement is associated with a primary key, but when explain uses the index of shop_id, I suspect that this shop_id index is the result. If you do not go to the index of this shop_id field, go straight to the primary key ID since it takes into account the join link and the subquery.

As a result, he tested, found a lot faster, problem solving, expain results as shown in the following ok.jpg, there is no using temporary this one.

Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.

Optimization case of using forced index in MySQL select Neutron query