WOW...what a waste

Store Builder Questions
1

this makes no sense… on a product view











why is cs-cart loading this 1000 times on every load ?


grep -c "96 Query       SELECT cscart_images.image_path, cscart_common_descriptions.description as alt" /var/log/mysql.log <br />
1966
```<br />
<br />
           ```php
              96 Query       SELECT pair_id, image_id, detailed_id FROM cscart_images_links WHERE object_id = 6679 AND object_type = 'product' AND type = 'M'<br />
                     96 Query       SELECT cscart_images.image_path, cscart_common_descriptions.description as alt, cscart_images.image_x, cscart_images.image_y FROM cscart_images LEFT JOIN cscart_common_descriptions ON cscart_common_descriptions.object_id = cscart_images.image_id AND cscart_common_descriptions.object_holder = 'images' AND cscart_common_descriptions.lang_code = 'FR'  WHERE cscart_images.image_id = 4237<br />
                     96 Query       SELECT cscart_images.image_path, cscart_common_descriptions.description as alt, cscart_images.image_x, cscart_images.image_y FROM cscart_images LEFT JOIN cscart_common_descriptions ON cscart_common_descriptions.object_id = cscart_images.image_id AND cscart_common_descriptions.object_holder = 'images' AND cscart_common_descriptions.lang_code = 'FR'  WHERE cscart_images.image_id = 4236<br />
                     96 Query       SELECT COUNT(*) FROM cscart_product_options as a LEFT JOIN cscart_product_global_option_links as c ON c.option_id = a.option_id WHERE (a.product_id = 6679 OR c.product_id = 6679) AND a.status = 'A'<br />
                     96 Query       SELECT required_id FROM cscart_product_required_products WHERE product_id = 6679<br />
                     96 Query       SELECT pair_id, image_id, detailed_id FROM cscart_images_links WHERE object_id = 6680 AND object_type = 'product' AND type = 'M'<br />
                     96 Query       SELECT cscart_images.image_path, cscart_common_descriptions.description as alt, cscart_images.image_x, cscart_images.image_y FROM cscart_images LEFT JOIN cscart_common_descriptions ON cscart_common_descriptions.object_id = cscart_images.image_id AND cscart_common_descriptions.object_holder = 'images' AND cscart_common_descriptions.lang_code = 'FR'  WHERE cscart_images.image_id = 4243<br />
                     96 Query       SELECT cscart_images.image_path, cscart_common_descriptions.description as alt, cscart_images.image_x, cscart_images.image_y FROM cscart_images LEFT JOIN cscart_common_descriptions ON cscart_common_descriptions.object_id = WHERE cscart_images.image_id = 4690<br />
                     96 Query       SELECT COUNT(*) FROM cscart_product_options as a LEFT JOIN cscart_product_global_option_links as c ON c.option_id = a.option_id WHERE (a.product_id = 6762 OR c.product_id = 6762) AND a.status = 'A'<br />
                     96 Query       SELECT required_id FROM cscart_product_required_products WHERE product_id = 6762<br />
                     96 Query       SELECT pair_id, image_id, detailed_id FROM cscart_images_links WHERE object_id = 6763 AND object_type = 'product' AND type = 'M'<br />
                     96 Query       SELECT cscart_images.image_path, cscart_common_descriptions.description as alt, cscart_images.image_x, cscart_images.image_y FROM cscart_images LEFT JOIN cscart_common_descriptions ON cscart_common_descriptions.object_id = cscart_images.image_id AND cscart_common_descriptions.object_holder = 'images' AND cscart_common_descriptions.lang_code = 'FR'  WHERE cscart_images.image_id = 4697<br />
                     96 Query       SELECT cscart_images.image_path, cscart_common_descriptions.description as alt, cscart_images.image_x, cscart_images.image_y FROM cscart_images LEFT JOIN cscart_common_descriptions ON cscart_common_descriptions.object_id = cscart_images.image_id AND cscart_common_descriptions.object_holder = 'images' AND cscart_common_descriptions.lang_code = 'FR'  WHERE cscart_images.image_id = 4696<br />
                     96 Query       SELECT COUNT(*) FROM cscart_product_options as a LEFT JOIN cscart_product_global_option_links as c ON c.option_id = a.option_id WHERE (a.product_id = 6763 OR c.product_id = 6763) AND a.status = 'A'<br />
                     96 Query       SELECT required_id FROM cscart_product_required_products WHERE product_id = 6763<br />
                     96 Query       SELECT pair_id, image_id, detailed_id FROM cscart_images_links WHERE object_id = 6764 AND object_type = 'product' AND type = 'M'<br />
                     96 Query       SELECT cscart_images.image_path, cscart_common_descriptions.description as alt, cscart_images.image_x, cscart_images.image_y FROM cscart_images LEFT JOIN cscart_common_descriptions ON cscart_common_descriptions.object_id = cscart_images.image_id AND cscart_common_descriptions.object_holder = 'images' AND cscart_common_descriptions.lang_code = 'FR'  WHERE cscart_images.image_id = 4703<br />
                     96 Query       SELECT cscart_images.image_path, cscart_common_descriptions.description as alt, cscart_images.image_x, cscart_images.image_y FROM cscart_images LEFT JOIN cscart_common_descriptions ON cscart_common_descriptions.object_id = cscart_images.image_id AND cscart_common_descriptions.object_holder = 'images' AND cscart_common_descriptions.lang_code = 'FR'  WHERE cscart_images.image_id = 4702<br />
                     96 Query       SELECT COUNT(*) FROM cscart_product_options as a LEFT JOIN cscart_product_global_option_links as c ON c.option_id = a.option_id WHERE (a.product_id = 6764 OR c.product_id = 6764) AND a.status = 'A'<br />
                     96 Query       SELECT required_id FROM cscart_product_required_products WHERE product_id = 6764<br />
                     96 Query       SELECT pair_id, image_id, detailed_id FROM cscart_images_links WHERE object_id = 6765 AND object_type = 'product' AND type = 'M'<br />
                     96 Query       SELECT cscart_images.image_path, cscart_common_descriptions.description as alt, cscart_images.image_x, cscart_images.image_y FROM cscart_images LEFT JOIN cscart_common_descriptions ON cscart_common_descriptions.object_id = cscart_images.image_id AND cscart_common_descriptions.object_holder = 'images' AND cscart_common_descriptions.lang_code = 'FR'  WHERE cscart_images.image_id = 4709<br />
                     96 Query       SELECT cscart_images.image_path, cscart_common_descriptions.description as alt, cscart_images.image_x, cscart_images.image_y FROM cscart_images LEFT JOIN cscart_common_descriptions ON cscart_common_descriptions.object_id = cscart_images.image_id AND cscart_common_descriptions.object_holder = 'images' AND cscart_common_descriptions.lang_code = 'FR'  WHERE cscart_images.image_id = 4708<br />
                     96 Query       SELECT COUNT(*) FROM cscart_product_options as a LEFT JOIN cscart_product_global_option_links as c ON c.option_id = a.option_id WHERE (a.product_id = 6765 OR c.product_id = 6765) AND a.status = 'A'<br />
                     96 Query       SELECT required_id FROM cscart_product_required_products WHERE product_id = 6765<br />
                     96 Query       SELECT pair_id, image_id, detailed_id FROM cscart_images_links WHERE object_id = 6766 AND object_type = 'product' AND type = 'M'<br />
                     96 Query       SELECT cscart_images.image_path, cscart_common_descriptions.description as alt, cscart_images.image_x, cscart_images.image_y FROM cscart_images LEFT JOIN cscart_common_descriptions ON cscart_common_descriptions.object_id = cscart_images.image_id AND cscart_common_descriptions.object_holder = 'images' AND cscart_common_descriptions.lang_code = 'FR'  WHERE cscart_images.image_id = 4715<br />
                     96 Query       SELECT cscart_images.image_path, cscart_common_descriptions.description as alt, cscart_images.image_x, cscart_images.image_y FROM cscart_images LEFT JOIN cscart_common_descriptions ON cscart_common_descriptions.object_id = cscart_images.image_id AND cscart_common_descriptions<br />
<br />

```<br />
<br />
<br />
<br />
<br />
<br />
<br />
```php
<br />
# grep -c " 92 Query       SELECT c.id_path FROM cscart_products_categories as pc LEFT JOIN cscart_categories as c O" /var/log/mysql.log <br />
982
```<br />
<br />
<br />
```php
2 Query       SELECT c.id_path FROM cscart_products_categories as pc LEFT JOIN cscart_categories as c ON pc.category_id = c.category_id WHERE pc.product_id = 5889 AND pc.link_type = 'M'<br />
                     92 Query       SELECT c.id_path FROM cscart_products_categories as pc LEFT JOIN cscart_categories as c ON pc.category_id = c.category_id WHERE pc.product_id = 5892 AND pc.link_type = 'M'<br />
                     92 Query       SELECT c.id_path FROM cscart_products_categories as pc LEFT JOIN cscart_categories as c ON pc.category_id = c.category_id WHERE pc.product_id = 5894 AND pc.link_type = 'M'<br />
                     92 Query       SELECT c.id_path FROM cscart_products_categories as pc LEFT JOIN cscart_categories as c ON pc.category_id = c.category_id WHERE pc.product_id = 5895 AND pc.link_type = 'M'<br />
                     92 Query       SELECT c.id_path FROM cscart_products_categories as pc LEFT JOIN cscart_categories as c ON pc.category_id = c.category_id WHERE pc.product_id = 5897 AND pc.link_type = 'M'<br />
                     92 Query       SELECT c.id_path FROM cscart_products_categories as pc LEFT JOIN cscart_categories as c ON pc.category_id = c.category_id WHERE pc.product_id = 5898 AND pc.link_type = 'M'<br />
110518 14:35:59      92 Query       SELECT c.id_path FROM cscart_products_categories as pc LEFT JOIN cscart_categories as c ON pc.category_id = c.category_id WHERE pc.product_id = 5899 AND pc.link_type = 'M'<br />
                     92 Query       SELECT c.id_path FROM cscart_products_categories as pc LEFT JOIN cscart_categories as c ON pc.category_id = c.category_id WHERE pc.product_id = 5900 AND pc.link_type = 'M'<br />
                     92 Query       SELECT c.id_path FROM cscart_products_categories as pc LEFT JOIN cscart_categories as c ON pc.category_id = c.category_id WHERE pc.product_id = 5901 AND pc.link_type = 'M'<br />
                     92 Query       SELECT c.id_path FROM cscart_products_categories as pc LEFT JOIN cscart_categories as c ON pc.category_id = c.category_id WHERE pc.product_id = 5902 AND pc.link_type = 'M'<br />
                     92 Query       SELECT c.id_path FROM cscart_products_categories as pc LEFT JOIN cscart_categories as c ON pc.category_id = c.category_id WHERE pc.product_id = 5903 AND pc.link_type = 'M'<br />
                     92 Query       SELECT c.id_path FROM cscart_products_categories as pc LEFT JOIN cscart_categories as c ON pc.category_id = c.category_id WHERE pc.product_id = 5904 AND pc.link_type = 'M'<br />
                     92 Query       SELECT c.id_path FROM cscart_products_categories as pc LEFT JOIN cscart_categories as c ON pc.category_id = c.category_id WHERE pc.product_id = 5907 AND pc.link_type = 'M'<br />
                     92 Query       SELECT c.id_path FROM cscart_products_categories as pc LEFT JOIN cscart_categories as c ON pc.category_id = c.category_id WHERE pc.product_id = 5908 AND pc.link_type = 'M'<br />
                     92 Query       SELECT c.id_path FROM cscart_products_categories as pc LEFT JOIN cscart_categories as c ON pc.category_id = c.category_id WHERE pc.product_id = 5909 AND pc.link_type = 'M'<br />
                     92 Query       SELECT c.id_path FROM cscart_products_categories as pc LEFT JOIN csca
```<br />
<br />
etc....
2

What tool is producing this output?



In the first case, it looks as if it is retrieving the image pairs, product options and other product info for some product listing you’ve requested. There could be 10-15 queries per product easily depending on features, filters, options, imagery, option imagery, etc.



The second batch looks like it’s retrieving the category paths for another (or maybe the same) product listing.



Etc…



You’re providing limited info and implying reasonableness concerns without providing enough info for someone to validate your fears.



So much is dependent upon how you have your cart configured, what blocks you use, how many features/filters you have, etc. The cart can place a HUGE load on mysql if you use lots of the cart’s features. The data has to come from somewhere.

3

tool is not a tool :wink: it’s mysql options

/etc/my.cnf

[mysqld]
log_slow_queries=/var/log/mysqld.slow.log
log=/var/log/mysql.log



also… first was for a product (only one product as in …the dispatch view product…

4

So which of the selects in the first chunk do you think are unnecessary?

Not knowing your configuration and what addons you have enabled, the requests above seem reasonable (to me).



Note that imagery for options has been a fairly recent addition and undoubtedly places a bigger load in resolving those otpions and their releated images.



So if you have a product with 10 options, there will be 10 requests for the pair_id, and then each image of the identified pair. So that would be 30 requests.



That’s it’s in the slow query log is separate issue. Looks like maybe the indexing could be improved.

5

grep -c “96 Query SELECT cscart_images.image_path, cscart_common_descriptions.description as alt” /var/log/mysql.log

1966



index.php?dispatch=products.view&product_id=5867







MEANS 1966 ROWS for one product…



now you can imagine a category page with 16 products will ask for 32000 rows which is unacceptable.



ALSO…



if you look a bit… youll see that product_id increments on each row… meaning its pulling for ALL Products instead of ONE…



i can’t figure which core files are doing this mysql query.



i do see that this is part of the prob



addons/seo/func.php: $id_path = db_get_field(“SELECT c.id_path FROM ?:products_categories as pc LEFT JOIN ?:categories as c ON pc.category_id = c.category_id WHERE pc.product_id = ?i AND pc.link_type = ‘M’”, $object_id);

6

fn.catalog.php 2.1.1



Does anyone have fn.catalog.php 2.1.1 so i can fix this problem ?

7

ok it’s something in right block… i disabled and all ok now, i have no time to debug this since i need 2.1.1 → 2.1.2 → 2.1.3 → 2.1.4



and then fix it all back to our needs

8

would you like to share how to fix this?

Thanks

9

The products.view controller will use the function fn_get_product_data in fn.catalog.php.

Blocks will use fn_get_products() in fn.catalog.php.

Note that fn_get_products() is heavily “hooked”. By this I mean that many addons have a fn__get_products function defined and an entry for ‘get_products’ in their register_hooks() function normally in init.php within the addon.



The function usually ends up with a huge number of joins since each addon is looking for something different and more than likely some are not very smart about what they do.