Optimize an SQL statement! SQLcodeselecta. id, B. idasmycompid, B. typeid, B. co_end,. pro_price,. pro_nameasrealproname,. discount_rate,. bigpic,. temp_url, B. co_name, c. pro optimizes an SQL statement!
SQL code
select a.id,b.id as mycompid,b.typeid,b.co_end,a.pro_price,a.pro_name as realproname,a.discount_rate,a.bigpic,a.temp_url,b.co_name,c.pro_name,d.city_name,a.begindate,a.modifydate,a.pro_intro from product a left join company b on(a.userid=b.id) left join pro c on(b.proid=c.id) left join city d on(b.cityid=d.id) left join protype e on(a.typeid=e.id) where (a.pro_name like binary '%%' or a.pro_intro like binary '%%' or a.keywords like binary '%%') and co_type=112 and b.islock=0 and a.isdel=0 and a.comp_state=1 and b.islock=0 order by b.realtypeid desc,a.modifydate desc limit 0,20
The following is a simple explanation of this sentence. on the product list page, 60 thousand company is the enterprise table. 40 thousand pro is the province information, city is the city information, and protype is the product category information,
I mainly want to sort the search results by enterprise level. if the search results are of the same level, they are sorted by product release time. so I wrote such an SQL statement, but the efficiency is really low, estimated execution time in 6 seconds
The main reason for my test is that order by B. realtypeid desc, a. modifydate desc.
However, this sorting must be arranged. please help me come up with ideas. what should I do !~
------ Solution --------------------
Walking by and looking at your fingers
------ Solution --------------------
Let me take a look
------ Solution --------------------
When the data volume is large, we recommend that you retrieve data in batches.
Will it be faster?
Select. id, B. id as mycompid, B. typeid, B. co_end,. pro_price,. pro_name as realproname,. discount_rate,. bigpic,. temp_url, B. co_name, c. pro_name, d. city_name,. begindate,. modifydate,. pro_intro from product a, company B, protype e
Left join pro c on (B. proid = c. id)
Left join city d on (B. cityid = d. id)
Where. userid = B. id and. typeid = e. id and (. pro_name like binary '%' or. pro_intro like binary '%' or. keywords like binary '%') and co_type = 112 and B. islock = 0 and. isdel = 0 and. comp_state = 1 and B. islock = 0 order by B. realtypeid desc,. modifydate desc limit 0, 20
------ Solution --------------------
B. realtypeid, a. modifydate create an index for the two columns ..
------ Solution --------------------
View is recommended!
------ Solution --------------------
If the data is too big, it is not good to extract the data step by step and use PHP to process it. I can't explain it here,
------ Solution --------------------
The sorting of segmented indexes in sorting bar is very slow.
------ Solution --------------------
The problem is that in left join
It is too large to perform continuous left join for so many tables with a large amount of data...
------ Solution --------------------
You can try to add indexes to fields that are commonly used as conditions.
------ Solution --------------------
(A. pro_name like binary '%' or a. pro_intro like binary '%' or a. keywords like binary '% ')
What does that mean ??
