Hello!

There is a simple request:

SELECT ppv.id, ppv.property_id, ppv.value_id, ppv.text, p.edit_price, p.title, p.type, p.view, p.default, p.prefix, p.suffix FROM vqbfa_properties AS p LEFT JOIN vqbfa_product_properties_values AS ppv ON ppv.property_id=p.id WHERE p.published=1 GROUP BY p.id ORDER BY p.ordering;

its EXPLAIN - http://clip2net.com/s/i7vF6C profiling - http://clip2net.com/s/i7vGaq

as you can see, its execution takes 244.3 ms. I can’t understand why it takes so long. The corresponding indices and links are spelled out.

  • and how much data is returned? in the same place sending of data takes this time. maybe there are a couple of thousand lines ... upd, well, here's a plan, there are a lot of data, that's slow. Are you sure you need everything? - Yura Ivanov
  • Yes, there is a lot of data there, in the table of links there are about 180 thousand lines, when queried it bypasses 6 with a penny of thousands. I looked at the number of lines after I wrote here. I thought I could somehow optimize the query, suddenly I missed something) Data is needed all the way a filter of goods is formed on their basis. - TakT
  • Indexes on columns in the condition and groupings are there? - Nik555
  • Yes, there are. - TakT
  • @TakT, nevertheless, I think, it is better to reduce the sample. split the request into two: properties - 14 records, and values ​​- 6k. those. remove p.edit_price, p.title, p.type, p.view, p.default, p.prefix, p.suffix from this request, leave only p.id , and in the code take property details. Well, then the optimization option is caching. the request is still pseudo-static, changes in values ​​and even more so in properties do not occur so often. - Yura Ivanov

0