Order list very slow after upgrade to 1.7.8.2

[dwilden]

Hi Guys,

After upgrade my ps 1.6 to 1.7.8.2, the BO orders list takes about 21s to show it.

I have test to upgrade to 1.7.6.9 and the BO orders list work well, about 1,2s

Testing the query on the database, this times are confirmed too.

Query from 1.7.8.2 about 21s

SELECT
	CONCAT(
		LEFT (cu.`firstname`, 1),
		'. ',
		cu.`lastname`
	) AS `customer`,
	o.id_order,
	o.reference,
	o.total_paid_tax_incl,
	os.paid,
	osl. NAME AS osname,
	o.id_currency,
	cur.iso_code,
	o.current_state,
	o.id_customer,
	cu.`id_customer` IS NULL AS `deleted_customer`,
	os.color,
	o.payment,
	s. NAME AS shop_name,
	o.date_add,
	cu.company,
	cl. NAME AS country_name,
	o.invoice_number,
	o.delivery_number,
	(
		SELECT

		IF (count(so.id_order) > 0, 0, 1)
		FROM
			ps_orders so
		WHERE
			(
				so.id_customer = o.id_customer
			)
		AND (so.id_order < o.id_order)
		LIMIT 1
	) AS new
FROM
	ps_orders o
LEFT JOIN ps_customer cu ON o.id_customer = cu.id_customer
LEFT JOIN ps_currency cur ON o.id_currency = cur.id_currency
INNER JOIN ps_address a ON o.id_address_delivery = a.id_address
LEFT JOIN ps_order_state os ON o.current_state = os.id_order_state
LEFT JOIN ps_shop s ON o.id_shop = s.id_shop
INNER JOIN ps_country c ON a.id_country = c.id_country
INNER JOIN ps_country_lang cl ON c.id_country = cl.id_country
AND cl.id_lang = 3
LEFT JOIN ps_order_state_lang osl ON os.id_order_state = osl.id_order_state
AND osl.id_lang = 3
WHERE
	o.`id_shop` IN ('1')
ORDER BY
	o.id_order DESC
LIMIT 50

Query from 1.7.6.9 about 1,2s

SELECT SQL_CALC_FOUND_ROWS
	a.`id_order`,
	`reference`,
	`total_paid_tax_incl`,
	`payment`,
	a.`date_add` AS `date_add`,
	a.id_currency,
	a.id_order AS id_pdf,
	CONCAT(
		LEFT (c.`firstname`, 1),
		'. ',
		c.`lastname`
	) AS `customer`,
	osl.`name` AS `osname`,
	os.`color`,

IF (
	(
		SELECT
			so.id_order
		FROM
			`ps_orders` so
		WHERE
			so.id_customer = a.id_customer
		AND so.id_order < a.id_order
		LIMIT 1
	) > 0,
	0,
	1
) AS new,
 country_lang. NAME AS cname,

IF (a.valid, 1, 0) badge_success,
 shop. NAME AS shop_name
FROM
	`ps_orders` a
LEFT JOIN `ps_customer` c ON (
	c.`id_customer` = a.`id_customer`
)
INNER JOIN `ps_address` address ON address.id_address = a.id_address_delivery
INNER JOIN `ps_country` country ON address.id_country = country.id_country
INNER JOIN `ps_country_lang` country_lang ON (
	country.`id_country` = country_lang.`id_country`
	AND country_lang.`id_lang` = 3
)
LEFT JOIN `ps_order_state` os ON (
	os.`id_order_state` = a.`current_state`
)
LEFT JOIN `ps_order_state_lang` osl ON (
	os.`id_order_state` = osl.`id_order_state`
	AND osl.`id_lang` = 3
)
LEFT JOIN `ps_shop` shop ON a.`id_shop` = shop.`id_shop`
WHERE
	1
AND a.id_shop IN (1)
ORDER BY
	a.id_order DESC
LIMIT 0,
 50

If I change the query of 1.7.8.2 to this one, works fine

SELECT
	CONCAT(
		LEFT (cu.`firstname`, 1),
		'. ',
		cu.`lastname`
	) AS `customer`,
	o.id_order,
	o.reference,
	o.total_paid_tax_incl,
	os.paid,
	osl. NAME AS osname,
	o.id_currency,
	cur.iso_code,
	o.current_state,
	o.id_customer,
	cu.`id_customer` IS NULL AS `deleted_customer`,
	os.color,
	o.payment,
	s. NAME AS shop_name,
	o.date_add,
	cu.company,
	cl. NAME AS country_name,
	o.invoice_number,
	o.delivery_number,
IF (
	(
		SELECT
			so.id_order
		FROM
			`ps_orders` so
		WHERE
			so.id_customer = o.id_customer
		AND so.id_order < o.id_order
		LIMIT 1
	) > 0,
	0,
	1
) AS new

FROM
	ps_orders o
LEFT JOIN ps_customer cu ON o.id_customer = cu.id_customer
LEFT JOIN ps_currency cur ON o.id_currency = cur.id_currency
INNER JOIN ps_address a ON o.id_address_delivery = a.id_address
LEFT JOIN ps_order_state os ON o.current_state = os.id_order_state
LEFT JOIN ps_shop s ON o.id_shop = s.id_shop
INNER JOIN ps_country c ON a.id_country = c.id_country
INNER JOIN ps_country_lang cl ON c.id_country = cl.id_country
AND cl.id_lang = 3
LEFT JOIN ps_order_state_lang osl ON os.id_order_state = osl.id_order_state
AND osl.id_lang = 3
WHERE
	o.`id_shop` IN ('1')
ORDER BY
	o.id_order DESC
LIMIT 50

 

How can change the code to fix this query on PS 1.7.8.2 ??

Thanks,

Daniel

 

[Diwad]

Hi. Did you find the solution?

[dwilden]

No solved with the direct upgrade from 1.6 to 1.7.8.2.

After aborting with the last attempts, I Use a migration method with this paid module.
https://prestahero.com/upgrade-data-migration/149-prestashop-better-upgrade-and-migrate-tool.html

Check the website because have a free module too.

I migrate the shop successfully from 1.6 to 1.7.8.5

Edited June 24, 2022 by dwilden (see edit history)