Hello,

If you say query changes the product details performance that might be due to some index, and it can happen.

If you are saying that it changed the performance on other pages too then No it has nothing to do with other pages, the cause might be different.

Thank you

Mian Saleem the performace reduce I notice only on product detail page.

Hello,

Yes, that might be query. Please let me know the server details to check.

Thank you

Mian Saleem Hello support,

any update?

Thank you

I am sorry for the late reply. I will check tomorrow morning and update you. Thanks

Hello,

This is the query used to get the transfers for the item

SELECT
	sma_transfers.date,
	transfer_no,
	(
		CASE WHEN sma_transfers.status = 'completed' THEN
			GROUP_CONCAT(CONCAT(sma_purchase_items.product_name, '__', sma_purchase_items.quantity) SEPARATOR '___')
		ELSE
			GROUP_CONCAT(CONCAT(sma_transfer_items.product_name, '__', sma_transfer_items.quantity) SEPARATOR '___')
		END) AS iname,
	from_warehouse_name AS fname,
	from_warehouse_code AS fcode,
	to_warehouse_name AS tname,
	to_warehouse_code AS tcode,
	grand_total,
	sma_transfers.status,
	sma_transfers.id AS id
FROM
	`sma_transfers`
	LEFT JOIN `sma_transfer_items` ON `sma_transfer_items`.`transfer_id` = `sma_transfers`.`id`
	LEFT JOIN `sma_purchase_items` ON `sma_purchase_items`.`transfer_id` = `sma_transfers`.`id`
WHERE ((sma_purchase_items.product_id = 56)
	OR(sma_transfer_items.product_id = 56))
GROUP BY
	`sma_transfers`.`id`
ORDER BY
	`sma_transfers`.`date` DESC
LIMIT 10;

Please try to run it on your database and check how long it takes?

Then try create index on transfer_id

CREATE INDEX transfer_id ON sma_purchase_items (transfer_id);

Now to run the query again and check the time again.

Once you created the index, the load time on your site should be better.

In case that is still not acceptable then please consider adding resources to your database server.

Thank you

Mian Saleem Hello,

at first full test the result is like this : Showing rows 0 - 4 (5 total, Query took 4.4773 seconds.)

where should I put this line of code ? >> CREATE INDEX transfer_id ON sma_purchase_items (transfer_id);

Same as other query, you can run the query from phpMyAdmin or tableplus or directly on your database. Thanks

Mian Saleem oh I run already in SQL and it is quick. so what else I could do?

Hello,

Nothing! If you afford you can consider adding resources to your server.

I am saying this because the same query took 23ms (4.48 seconds on your server) on your database before index and after index few microseconds on my machine.

Thank you