Slows down mysql query

Question

MySQL

There is mysql query:

SELECT p.`id`, p.`name`, p.`category_id`, v.`name` AS vendor, p.`price_ya`, pp.`value` AS price, pp.`fix_currency_id` , (SELECT ph.`id` FROM `products_photos` AS ph WHERE ph.`product_id`=p.`id` ORDER BY ph.`pos` ASC LIMIT 1) AS photo, px.`status`
FROM `products_prices` AS pp, `xml_products` AS px, `products` AS p LEFT JOIN `vendors` AS v ON p.`vendor_id`=v.`id`
WHERE pp.`price_id`='2' AND p.`id`=pp .`product_id` AND p.`id`=px.`product_id` AND px.`site_id`='2'

When run through phpmyadmin it runs in no time. When run on a virtual server from a script, it also works smartly. But having bought a dedicated server a couple of days ago, this query in the script, which was executed on a virtual machine in summer, here loads the MySQL server by 100% and is executed for 50 or more seconds.

The EXPLAIN of the query is:

id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY px ALL PRIMARY NULL NULL NULL 4660 Using where
1 PRIMARY p eq_ref PRIMARY PRIMARY 4 xpert_cp.px.product_id 1
1 PRIMARY v eq_ref PRIMARY PRIMARY 3 xpert_cp.p.vendor_id 1
1 PRIMARY pp eq_ref PRIMARY PRIMARY 6 xpert_cp.p.id,const 1 Using where
2 DEPENDENT SUBQUERY ph index product_id pos 1 NULL 1 Using where

Answer 1

Does it slow down without a subquery? Here it seems to me that the reason is that for each selected record, you have to do a search of another table.

Answer 2

Avoid complex queries. Logically, 1 product has 1 photo and 1 price, which means that information about the first photo is easier to store in the product description and update it when updating the photo, as well as information about the price.
Ideally, you should get all the necessary information by product id.

Answer 3

As I understand it, the request is made to pull out the first photo? The fact is that ORDER BY always makes tables tmp for sorting, although I don’t see it in EXPLAIN in this case.
Try like this:

SELECT 
  p.`id`,
  p.`name`,
  p.`category_id`,
  v.`name` AS vendor,
  p.`price_ya`,
  pp.`value` AS price,
  pp.`fix_currency_id`,
  ph.`id` AS photo,
  px.`status` 
FROM
  `products_prices` AS pp,
  `xml_products` AS px,
  `products` AS p 
  LEFT JOIN `vendors` AS v 
    ON p.`vendor_id` = v.`id` 
  LEFT JOIN `products_photos` ph
    ON ph.`product_id` = p.`id`
WHERE pp.`price_id` = '2' 
  AND p.`id` = pp.`product_id` 
  AND p.`id` = px.`product_id` 
  AND px.`site_id` = '2' 
GROUP BY p.`id`

Here GROUP By will group, and the first id will be released in the left join, if I'm not mistaken. Difficult to analyze without base.
You also need to take into account mysql versions, each version uses indexes differently, which by the way I don’t see at all, everywhere using where.

Answer 4

but try this, I write without checking, there may be errors (but I hope the meaning is clear):

SELECT 
  p.`id`, 
  p.`name`, 
  p.`category_id`, 
  v.`name` AS vendor, 
  p.`price_ya`, 
  pp.`value` AS price, 
  pp.`fix_currency_id`,
  ph.`id` AS `photo`,
  px.`status`
FROM 
  `products_prices` AS pp, 
  `xml_products` AS px
LEFT JOIN (
  SELECT 
    sph.`product_id`, 
    MIN(sph.`pos`) AS min_pos
  FROM `products_photos` AS sph
  GROUP BY sph.`product_id`
) AS tph
  ON tph.`product_id` = p.`id`
LEFT JOIN `products_photos` AS ph 
  ON ph.`product_id` = tph .`product_id` 
  AND ph.`pos` = tph.`min_pos`
LEFT JOIN `vendors` AS v 
  ON p.`vendor_id`=v.`id` 
WHERE 
  pp.`price_id`='2' 
  AND p.`id`=pp.`product_id` 
  AND p.`id`=px.`product_id` 
  AND px.`site_id`='2'

PS It is assumed that the product_id + pos link is unique.

Answer 5

As far as I remember, in the case of a dependent subquery, it will be executed every time for each found record of the main query. The main one is also heavy, for some reason mysql decided to start parsing from the px table, apparently it thinks that it will turn out the most effective filter, but then it’s not clear why it suggests using a primer index (by the way, what is it?), but does not use it - insufficient filtering?
I suggest trying to get rid of the subquery in this form, analyze the tables and see what explain will give in this case. See how many records actually remain for each table separately after applying the necessary filters. Perhaps the optimizer makes a mistake in the order in which queries are executed and it needs to be directly indicated in what sequence it should be done. It’s a mystery to me why he still starts with px and runs through it all - about 4660 records. Whether in other tables it is more that after filters?
Look at the server parameters, run some thread with a tuner utility, for example mysqltuner.pl, correct any buffers. It is also possible that you previously had a server with a heated cache, but on the instance it was clean as glass.

Answer 6

And the InnoDB storage and the server is low on memory?