MySQL Statement optimization tips

Sorting results in slower performance

Optimization strategy: 1. Try not to use sort 2. Only the results of the index are checked and the internal connection query

Select bizchance0_.* from Biz_chance bizchance0_, Biz_bizcustomer bizbizcust1_
where Bizchance0_.uuid=bizbizcust1_.recordinfoid and bizchance0_.ispublic=1 order by bizchance0_.orderkey Desc Li MIT 0, 10;

Time 33 seconds order by sort performance slow reason: Select Bizchance0_.* if only select Bizchance0_.uuid UUID with index performance improvement



Select Bizchance0_.uuid as UID from Biz_chance bizchance0_, Biz_bizcustomer bizbizcust1_
where Bizchance0_.uuid=bizbizcust1_.recordinfoid and bizchance0_.ispublic=1 order BY bizchance0_.orderkey DESC limit 0,10;
Time 3 seconds


SELECT * FROM Biz_chance as UU inner joins (select Bizchance0_.uuid as UID from biz_chance bizchance0_, Biz_bizcustomer bi Zbizcust1_
where Bizchance0_.uuid=bizbizcust1_.recordinfoid and bizchance0_.ispublic=1 order BY bizchance0_.orderkey DESC limit 0,10) as U on U.uid=uu.uuid

INNER join Biz_bizcustomer as Cus on uu.uuid=cus.recordinfoid

Consolidated statement


2. Filter conditions, incorrect order

SELECT * from Biz_customer as Cus

Left join biz_config400 as Conf on Conf.customerid=cus.uuid
Left joins Biz_billinginfo as Bill on Bill.configid=conf.uuid and Bill.customerid=cus.uuid

spents 15 seconds

Reason: "and Bill.customerid=cus.uuid"

Optimization results

Select cus.* from Biz_customer as Cus

Left join biz_config400 as Conf on Conf.customerid=cus.uuid
Left joins Biz_billinginfo as Bill on Bill.configid=conf.uuid

where Bill.customerid=cus.uuid or bill.uuid is null

Or

Select cus.* from Biz_customer as Cus

Left join biz_config400 as Conf on Conf.customerid=cus.uuid
Left joins Biz_billinginfo as Bill on Bill.configid=conf.uuid

MySQL Statement optimization tips