Large amounts of Slow Queries In the Database

[goodwinmedia]

We have a large amount of slow queries we think are related to the extension. Can anyone confirm this or help us solve the issue or provide some guidance?

Here is a sample query:

Query_time: 8 Lock_time: 0 Rows_sent: 4 Rows_examined: 1766203

SET timestamp=1405533064; SELECT op.product_id, COUNT(*) AS total FROM order_product op LEFT JOIN order o ON (op.order_id = o.order_id) LEFT JOIN product p ON (op.product_id = p.product_id) LEFT JOIN product_hide AS ph ON p.product_id = ph.product_id AND ph.customer_group_id = '8' LEFT JOIN product_group_data AS pgd ON p.product_id = pgd.product_id AND pgd.customer_group_id = '8' LEFT JOIN product_to_store p2s ON (p.product_id = p2s.product_id) WHERE o.order_status_id > '0' AND p.status = '1' AND ph.product_id IS NULL AND COALESCE(pgd.date_available, p.date_available) <= NOW() AND p2s.store_id = '0' GROUP BY op.product_id ORDER BY total DESC LIMIT 4;

[Vinai]

No, I'm afraid (or actually I'm happy) to say that this query is not generated by any of my extensions. The attribute code I use is groupscatalog2_groups and the table names are groupscatalog_product_idx and groupscatalog_product_idx.

[goodwinmedia]

Thank you that is helpful. Any idea on how we can figure out what's causing this?

[Vinai]

Hm, the easiest approach to find the extension might be to simply search for files containing the string product_hide or product_group_data.

You can do so on the command line by running the following command in the Magento root directory:

grep -r product_hide .

Please note the dot at the end of the command.