ps_tag_count EXTREME SLOW LONG TIME QUERY

[Matteo]

This is how a PS store work with 5000 product.... its possible to have a 16 sec of query for a stupid thing like tag count?

 

# Time: 2018-05-02T09:17:03.724504Z
# User@Host: prestashop[prestashop] @ localhost [127.0.0.1]  Id:  1141
# Schema: Prestashop_1  Last_errno: 0  Killed: 0
# Query_time: 16.242101  Lock_time: 0.000379  Rows_sent: 0  Rows_examined: 4803138  Rows_affected: 204309
# Bytes_sent: 58
SET timestamp=1525252623;
REPLACE INTO `ps_tag_count` (id_group, id_tag, id_lang, id_shop, counter)
            SELECT cg.id_group, pt.id_tag, pt.id_lang, id_shop, COUNT(pt.id_tag) AS times
                FROM `ps_product_tag` pt
                INNER JOIN `ps_product_shop` product_shop
                    USING (id_product)
                JOIN (SELECT DISTINCT id_group FROM `ps_category_group`) cg
                WHERE product_shop.`active` = 1
                AND EXISTS(SELECT 1 FROM `ps_category_product` cp
                                LEFT JOIN `ps_category_group` cgo ON (cp.`id_category` = cgo.`id_category`)
                                WHERE cgo.`id_group` = cg.id_group AND product_shop.`id_product` = cp.`id_product`)

                GROUP BY pt.id_tag, pt.id_lang, cg.id_group, id_shop ORDER BY NULL;

[selectshop.at]

Which is your server environment ? With 5000 products you should run a nginx. All over 2.000 products runs with better performance on nginx server (proxied or as real ngnix machine).

Which php-version are you using ? Which Prestashop version ? Which mySQL version ? Mariadb in use, which version ? Furthermore if you have root rights you can optimize queries in DB (performance tuning on server settings) if they are too long for you (low queries are also depending from the quantity of data you are having in the table).

As I can see from all your topics added to forum you mainly are having problem with slow server. Slow servers are bad configured servers. I've customer running 20.000 products with over 2 Mio. filter options on nginx without any problem. BTW running PS 1.6. and not PS 1.7. which is still in development and will take a time to be moved 100% to new symfony framework.