performance issue in /classes/Cart.php

[otzy]

My database has more than 100000 products and more than 200000 images.
In some cases page load time was 40sec. and more

I have found that problem is in JOIN that contains IF function in ON clause.
This JOIN is located in function getProducts in /classes/Cart.php

If you have the same perfomance problem you can change SELECT:

$sql = '
       SELECT cp.`id_product_attribute`, cp.`id_product`, cp.`quantity` AS cart_quantity, pl.`name`,
       pl.`description_short`, pl.`available_now`, pl.`available_later`, p.`id_product`, p.`id_category_default`, p.`id_supplier`, p.`id_manufacturer`, p.`id_tax`, p.`on_sale`, p.`ecotax`,
       p.`quantity`, p.`price`, p.`reduction_price`, p.`reduction_percent`, p.`reduction_from`, p.`reduction_to`, p.`weight`, p.`out_of_stock`, p.`active`, p.`date_add`, p.`date_upd`,
       t.`id_tax`, tl.`name` AS tax, t.`rate`, pa.`price` AS price_attribute, pa.`quantity` AS quantity_attribute, 
       pa.`ecotax` AS ecotax_attr, 
   #FIX
   CASE WHEN i1.id_image IS NULL THEN i2.id_image ELSE i1.id_image END AS id_image,
   (SELECT legend FROM `ps_image_lang` WHERE id_lang=1 AND id_image=(CASE WHEN i1.id_image IS NULL THEN i2.id_image ELSE i1.id_image END) LIMIT 1) as legend,

# FIXED i.`id_image`, il.`legend`, 

pl.`link_rewrite`, cl.`link_rewrite` AS category, CONCAT(cp.`id_product`, cp.`id_product_attribute`) AS unique_id,
       IF (IFNULL(pa.`reference`, \'\') = \'\', p.`reference`, pa.`reference`) AS reference, 
       IF (IFNULL(pa.`supplier_reference`, \'\') = \'\', p.`supplier_reference`, pa.`supplier_reference`) AS supplier_reference, 
       (p.`weight`+ pa.`weight`) weight_attribute,
       IF (IFNULL(pa.`ean13`, \'\') = \'\', p.`ean13`, pa.`ean13`) AS ean13, pai.`id_image` AS \'pai_id_image\'
       FROM `'._DB_PREFIX_.'cart_product` cp
       LEFT JOIN `'._DB_PREFIX_.'product` p ON p.`id_product` = cp.`id_product`
       LEFT JOIN `'._DB_PREFIX_.'product_lang` pl ON (p.`id_product` = pl.`id_product` AND pl.`id_lang` = '.intval($this->id_lang).')
       LEFT JOIN `'._DB_PREFIX_.'product_attribute` pa ON (pa.`id_product_attribute` = cp.`id_product_attribute`)
       LEFT JOIN `'._DB_PREFIX_.'tax` t ON (t.`id_tax` = p.`id_tax`)
       LEFT JOIN `'._DB_PREFIX_.'tax_lang` tl ON (t.`id_tax` = tl.`id_tax` AND tl.`id_lang` = '.intval($this->id_lang).')

       LEFT JOIN `'._DB_PREFIX_.'product_attribute_image` pai ON (pai.`id_product_attribute` = pa.`id_product_attribute`)

       #FIX
       LEFT JOIN `ps_image` i1 ON (i1.id_image=pai.id_image AND i1.id_product=cp.id_product)
       LEFT JOIN `ps_image` i2 ON (i2.id_product=p.id_product AND i2.`cover`=1)

/* FIXED
       LEFT JOIN `'._DB_PREFIX_.'image` i ON (IF(pai.`id_image`,
               i.`id_image` =
               (SELECT i2.`id_image`
               FROM `'._DB_PREFIX_.'image` i2
               INNER JOIN `'._DB_PREFIX_.'product_attribute_image` pai2 ON (pai2.`id_image` = i2.`id_image`)
               WHERE i2.`id_product` = p.`id_product` AND pai2.`id_product_attribute` = pa.`id_product_attribute`
               ORDER BY i2.`position`
               LIMIT 1),
               i.`id_product` = p.`id_product` AND i.`cover` = 1)
       )

       LEFT JOIN `'._DB_PREFIX_.'image_lang` il ON (i.`id_image` = il.`id_image` AND il.`id_lang` = '.intval($this->id_lang).')
*/
       LEFT JOIN `'._DB_PREFIX_.'category_lang` cl ON (p.`id_category_default` = cl.`id_category` AND cl.`id_lang` = '.intval($this->id_lang).')
       WHERE `id_cart` = '.intval($this->id).'
       '.($id_product ? ' AND cp.`id_product` = '.intval($id_product) : '').'
       AND p.`id_product` IS NOT NULL
       GROUP BY unique_id
       ORDER BY cp.date_add ASC';

[chrisxm]

Thanks for the tip Otzy !

[otzy]

Thanks for the tip Otzy !

4 hours of mining

There is the same bug in v1.4

[mecollectibles]

Would you please tell the line that should be checked & also the version of Prestashop?

[otzy]

classes/Cart.php - function getProducts (line 245 in PS 1.3.5.0, line 300 in PS 1..4)

[mecollectibles]

does it work with version 1.3.2 too?

[mecollectibles]

Your code is very complicated. You talk about a line to make 1 change but give the whole code & we should search to see exactly what different than our code to change & if that is not the fact, why all these lines brought?
I think such bringing of many lines is not for here where most those refer are not specialists & not have much time too. If we wanted to search between all your lines, we were going to PHP classes to learn it & no need to come here then! ;-))))

[mecollectibles]

I gave up. I do not think this advise is good. It just wasted my time compare all time to see the differences & the work seems so unprofessional that I doubt if I use it it will harm my website more!

[otzy]

This is SQL request. I cosider it has to be shown entirely in order to prevent errors while you will find where you must insert changed lines.

The new lines of code are under the comment #FIX (two new fields and two new JOIN)
Lines need to be removed are commented near the word FIXED

Concerning professionalism
this code:

LEFT JOIN `'._DB_PREFIX_.'image` i ON (IF(pai.`id_image`,....

is unprofessional because it requires the full scan of huge table

I have replaced that JOIN with the new one on key fields:

LEFT JOIN `ps_image` i1 ON (i1.id_image=pai.id_image AND i1.id_product=cp.id_product)
LEFT JOIN `ps_image` i2 ON (i2.id_product=p.id_product AND i2.`cover`=1)

[otzy]

It just wasted my time compare all time to see the differences

if you do not use versioning system try WinMerge to compare files.

[mecollectibles]

What helps beginners like me the usual way all programmers help that is like that:

in line say 25 replace the following
"the original text"
with:
"the suggested text"

line say 28 immediately after this line:
"the line in the file"
add the following lines:
"the suggested line(s)"

It was really so strange I see your suggestion so long & then you advise of a software that takes time to learn how to work with while most questions in this forum are urgent. If we were to go learn software while our websites are crashed, our life time may end & we not learned the way to correct the mistakes on our websites.
I have never seen such way in giving advise in Prestashop or oscommerce that I was using for a year & not seen in other software advises too. Really very strange.

[otzy]

You are right. But if you read carefully the first post, all you need is to replace query with the suggested one.

But you wanted to find differences (and I supposed you are familiar with php and sql). That's why I have posted changed lines and link to WinMerge.

So the single mistake I made in first post is the lack of Prestashop version.

[mecollectibles]

This is SQL request. I cosider it has to be shown entirely in order to prevent errors while you will find where you must insert changed lines.

The new lines of code are under the comment #FIX (two new fields and two new JOIN)
Lines need to be removed are commented near the word FIXED

Concerning professionalism
this code:

LEFT JOIN `'._DB_PREFIX_.'image` i ON (IF(pai.`id_image`,....

is unprofessional because it requires the full scan of huge table

I have replaced that JOIN with the new one on key fields:

LEFT JOIN `ps_image` i1 ON (i1.id_image=pai.id_image AND i1.id_product=cp.id_product)
LEFT JOIN `ps_image` i2 ON (i2.id_product=p.id_product AND i2.`cover`=1)

Let me see if I well understand it.
I should chaned the whole lines below with the 2 lines you suggested?

LEFT JOIN `'._DB_PREFIX_.'image` i ON (IF(pai.`id_image`,
i.`id_image` =
(SELECT i2.`id_image`
FROM `'._DB_PREFIX_.'image` i2
INNER JOIN `'._DB_PREFIX_.'product_attribute_image` pai2 ON (pai2.`id_image` = i2.`id_image`)
WHERE i2.`id_product` = p.`id_product` AND pai2.`id_product_attribute` = pa.`id_product_attribute`
ORDER BY i2.`position`
LIMIT 1),
i.`id_product` = p.`id_product` AND i.`cover` = 1)
)

[otzy]

Let me see if I well understand it.
I should chaned the whole lines below with the 2 lines you suggested?

LEFT JOIN `'._DB_PREFIX_.'image` i ON (IF(pai.`id_image`,
i.`id_image` =
(SELECT i2.`id_image`
FROM `'._DB_PREFIX_.'image` i2
INNER JOIN `'._DB_PREFIX_.'product_attribute_image` pai2 ON (pai2.`id_image` = i2.`id_image`)
WHERE i2.`id_product` = p.`id_product` AND pai2.`id_product_attribute` = pa.`id_product_attribute`
ORDER BY i2.`position`
LIMIT 1),
i.`id_product` = p.`id_product` AND i.`cover` = 1)
)

this line has to be deleted too:
LEFT JOIN `'._DB_PREFIX_.'image_lang` il ON (i.`id_image` = il.`id_image` AND il.`id_lang` = '.intval($this->id_lang).')

and in addition you must change code

i.`id_image`, il.`legend`, 

with this one:

    CASE WHEN i1.id_image IS NULL THEN i2.id_image ELSE i1.id_image END AS id_image,
   (SELECT legend FROM `ps_image_lang` WHERE id_lang=1 AND id_image=(CASE WHEN i1.id_image IS NULL THEN i2.id_image ELSE i1.id_image END) LIMIT 1) as legend,

so it would be better if you will change the whole SQL expression

[mecollectibles]

so it would be better if you will change the whole SQL expression

I replaced the SQL with the one you supplied above but my website crashed & I had to remove it & restore the original.

[otzy]

what is your PS version?
send me classes/Cart.php and I'll try to fix it.

[mecollectibles]

Version 1.3.2.3

I attach the file.

Cart.php

[otzy]

test attached file please.

Check that errors are enabled in /config/config.inc.php

<?php

/* Debug only */
ini_set('display_errors', 'on');
define('_PS_DEBUG_SQL_', true);

Cart.php

[mecollectibles]

I did as you suggested but I had one of my worst experiences in my website errors.
I prefer to give up now & do not follow this post at all.
I simply do not think this works for me. But thanks anyway.