I'm Using Presta 1.6.1.4. It's Very Slow When Deleting Products.

[Jillz]

I'm using presta 1.6.1.4. it's very slow when deleting products. Can someone help me out.

[FredoGT]

The same... any idea ?

[mexis]

Try this 

This tip will help you to optimize the operation speed of the function cleanPositions. This function is used in the standard import feature and it is loading the database pretty much due to a large number of ‘Update’ requests.

public static function cleanPositions($id_category)
{
$return = true;
 
$result = Db::getInstance()->executeS('
SELECT `id_product`
FROM `'._DB_PREFIX_.'category_product`
WHERE `id_category` = '.(int)$id_category.'
ORDER BY `position`
');
$total = count($result);
 
for ($i = 0; $i < $total; $i++)
$return &= Db::getInstance()->update('category_product', array(
'position' => $i,
), '`id_category` = '.(int)$id_category.' AND `id_product` = '.(int)$result[$i]['id_product']);
 
return $return;
}

This can become a serious problem if there is a large number of products in the directory, while the updated function works much faster.

public static function cleanPositions($id_category)
{
    $return = true;
    $result = Db::getInstance()->execute('
        update `'._DB_PREFIX_.'category_product` cp1 join (
           select id_category, id_product, @i := @i+1 new_position
           from `'._DB_PREFIX_.'category_product`, (select @i:=-1) temp
           where id_category = '.(int)$id_category.' order by position asc
        ) cp2 on cp1.id_category = cp2.id_category and cp1.id_product = cp2.id_product set cp1.position = cp2.new_position
');
 
    return $return;
}

Edited January 25, 2017 by mexis (see edit history)

[bimbiribelli]

Hi mexis i have problem with delete product prestashop i have prestashop 1.6.1.11

[satkauskas]

I have such code in product.php 1,6,1,3 version. I have same slow delete problem here:

 

/*
     * Reorder product position in category $id_category.
     * Call it after deleting a product from a category.
     *
     * @param int $id_category
     */
    public static function cleanPositions($id_category, $position = 0)
    {
        $return = true;


        if (!(int)$position) {
            $result = Db::getInstance()->executeS('
                SELECT `id_product`
                FROM `'._DB_PREFIX_.'category_product`
                WHERE `id_category` = '.(int)$id_category.'
                ORDER BY `position`
            ');
            $total = count($result);


            for ($i = 0; $i < $total; $i++) {
                $return &= Db::getInstance()->update(
                    'category_product',
                    array('position' => $i),
                    '`id_category` = '.(int)$id_category.' AND `id_product` = '.(int)$result[$i]['id_product']
                );
                $return &= Db::getInstance()->execute(
                    'UPDATE `'._DB_PREFIX_.'product` p'.Shop::addSqlAssociation('product', 'p').'
                    SET p.`date_upd` = "'.date('Y-m-d H:i:s').'", product_shop.`date_upd` = "'.date('Y-m-d H:i:s').'"
                    WHERE p.`id_product` = '.(int)$result[$i]['id_product']
                );
            }
        } else {
            $result = Db::getInstance()->executeS('
                SELECT `id_product`
                FROM `'._DB_PREFIX_.'category_product`
                WHERE `id_category` = '.(int)$id_category.' AND `position` > '.(int)$position.'
                ORDER BY `position`
            ');
            $total = count($result);
            $return &= Db::getInstance()->update(
                'category_product',
                array('position' => array('type' => 'sql', 'value' => '`position`-1')),
                '`id_category` = '.(int)$id_category.' AND `position` > '.(int)$position
            );


            for ($i = 0; $i < $total; $i++) {
                $return &= Db::getInstance()->execute(
                    'UPDATE `'._DB_PREFIX_.'product` p'.Shop::addSqlAssociation('product', 'p').'
                    SET p.`date_upd` = "'.date('Y-m-d H:i:s').'", product_shop.`date_upd` = "'.date('Y-m-d H:i:s').'"
                    WHERE p.`id_product` = '.(int)$result[$i]['id_product']
                );
            }
        }
        return $return;
    }

[Cnic]

On 1/25/2017 at 5:31 PM, mexis said:

Try this 

This tip will help you to optimize the operation speed of the function cleanPositions. This function is used in the standard import feature and it is loading the database pretty much due to a large number of ‘Update’ requests.

public static function cleanPositions($id_category)
{
$return = true;
 
$result = Db::getInstance()->executeS('
SELECT `id_product`
FROM `'._DB_PREFIX_.'category_product`
WHERE `id_category` = '.(int)$id_category.'
ORDER BY `position`
');
$total = count($result);
 
for ($i = 0; $i < $total; $i++)
$return &= Db::getInstance()->update('category_product', array(
'position' => $i,
), '`id_category` = '.(int)$id_category.' AND `id_product` = '.(int)$result[$i]['id_product']);
 
return $return;
}

This can become a serious problem if there is a large number of products in the directory, while the updated function works much faster.

public static function cleanPositions($id_category)
{
    $return = true;
    $result = Db::getInstance()->execute('
        update `'._DB_PREFIX_.'category_product` cp1 join (
           select id_category, id_product, @i := @i+1 new_position
           from `'._DB_PREFIX_.'category_product`, (select @i:=-1) temp
           where id_category = '.(int)$id_category.' order by position asc
        ) cp2 on cp1.id_category = cp2.id_category and cp1.id_product = cp2.id_product set cp1.position = cp2.new_position
');
 
    return $return;
}

I tried this and i jumped to 1 product per 1-2 seconds from 1 product per 45 seconds.

Thanks!