1

I've been doing some debugging lately as to why our sites been so slow whenever anyone clicks on a large category. Upon auditing the query being called, I noticed that all of the items were being queried all at once instead of as needed. Below is the query:

SELECT t_d.entity_id, t_d.attribute_id, t_d.value AS default_value, t_s.value AS store_value, IF(t_s.value_id IS NULL, t_d.value, t_s.value) AS value FROM catalog_product_entity_decimal AS t_d LEFT JOIN catalog_product_entity_decimal AS t_s ON t_s.attribute_id = t_d.attribute_id AND t_s.entity_id = t_d.entity_id AND t_s.store_id = 1 WHERE (t_d.entity_type_id = 4) AND (t_d.entity_id IN (6322, 6325, 9476, 11140, 5259, 5799, 10949, 5803, 10947, 10293, 10953, 5808, 10945, 10292, 5798, 11161, 10946, 5804, 10951, 10950, 5806, 11158, 11159, 11157, 11160, 10948, 10944, 10954, 10974, 10952, 10977, 10975, 5807, 5796, 10988, 10297, 5805, 10963, 8653, 8646, 5801, 8654, 10976, 10972, 5809, 5785, 10152, 5800, 10158, 10994, 10992, 10939, 10725, 10213, 10964, 10212, 10722, 6188, 6193, 6192, 10106, 10724, 6190, 6173, 6198, 6186, 6191, 6189, 6187, 6197, 6195, 8159, 10723, 8163, 10113, 6196, 6185, 8167, 8168, 8166, 8112, 8164, 8169, 8110, 10215, 10720, 10216, 10219, 10114, 5797, 10721, 10719, 11618, 8162, 6171, 8160, 6208, 8165, 6228, 6233, 6174, 6206, 11619, 6194, 6209, 6234, 6207, 11620, 10112, 10117, 10118, 11613, 11611, 6176, 11614, 6231, 6236, 11617, 6232, 8161, 10718, 10214, 10109, 10156, 10161, 11616, 10154, 10969, 10115, 10157, 10221, 10150, 10160, 11615, 10116, 10153, 10296, 8226, 10727, 8108, 10732, 10220, 8283, 10218, 10713, 10814, 8104, 10728, 10792, 8087, 10812, 8282, 8228, 10217, 8083, 8651, 8644, 10211, 10110, 6178, 11623, 10791, 8652, 10742, 6230, 6235, 11621, 6181, 10789, 6238, 6179, 11624, 11612, 11622, 10790, 10741, 6229, 10788, 10739, 10119, 8214, 8261, 8111, 10105, 10787, 8082, 8284, 8230, 8262, 8213, 8245, 8088, 6487, 10985, 8229, 6403, 10793, 6237, 6398, 6394, 10802, 10805, 8642, 13203, 13201, 10811, 13152, 5802, 10159, 13199, 13155, 10120, 13197, 10108, 13202, 10155, 10111, 13198, 10774, 8047, 10819, 10770, 8048, 8174, 6397, 6393, 6410, 6435, 6443, 6399, 6227, 6402, 10715, 6400, 6391, 6396, 6401, 6404, 6392, 10122, 10125, 10192, 10131, 10781, 10782, 10783, 2338, 10801, 10809, 8260, 6361, 8647, 8643, 10147, 10170, 2469, 8109, 8280, 10141, 13154, 6434, 6395, 10149, 13157, 6437, 6442, 13204, 6440, 13153, 6433, 13200, 11647, 13156, 6436, 6409, 6441, 6223, 6366, 6226, 6224, 6362, 6222, 2378, 8232, 8235, 11648, 11631, 11646, 11632, 10129, 8298, 8303, 8220, 10121, 8142, 8147, 6481, 6139, 6132, 6135, 6140, 6131, 6449, 6445, 6172, 6364, 6170, 6446, 6451, 6168, 10205, 10107, 10127, 10130, 10204, 10123, 10133, 10165, 10138, 10202, 13205, 10973, 2377, 10880, 10863, 11203, 10962, 10864, 11204, 10862, 10136, 8144, 8231, 8086, 8175, 8084, 8227, 8173, 8237, 8146, 8149, 8085, 8233, 6183, 6439, 6225, 6438, 6448, 6365, 8236, 8238, 6360, 2380, 2379, 10704, 10911, 10702, 10135, 6134, 10144, 6479, 6137, 10169, 8291, 10295, 8289, 6369, 6278, 6133, 6138, 6136, 6215, 6444, 6213, 6447, 6450, 6177, 11649, 6214, 6175, 6180, 6212, 10794, 10895, 10888, 10893, 10903, 10906, 10904, 10203, 8663, 6363, 6480, 6281, 8140, 8145, 8234, 8143, 8239, 8148, 8217, 8141, 8151, 8215, 11650, 6427, 6420, 6425, 11607, 10178, 10183, 8665, 10142, 10175, 6242, 6247, 11638, 6240, 8645, 6245, 6250, 6243, 6248, 6241, 6246, 6251, 6239, 6244, 8649, 6249, 10844, 6368, 6216, 8669, 8674, 10126, 6482, 6405, 6376, 10957, 11639, 11644, 6150, 10955, 6485, 6478, 11608, 11640, 10200, 199, 193, 198, 6422, 6430, 6428, 6419, 11645, 6407, 6252, 11633, 11606, 11643, 11636, 11641, 11634, 6455, 6182, 6406, 11605, 11610, 11642, 2363, 6316, 6309, 6378, 6383, 6386, 6310, 6315, 6379, 6426, 6308, 6318, 6424, 10737, 10198, 10171, 8091, 8719, 13150, 8279, 10167, 8092, 10172, 8737, 8107, 10128, 6390, 11637, 7637, 6483, 11635, 6375, 6452, 11609, 6408, 6374, 6411, 6456, 10151, 10174, 7645, 8113, 7633, 7632, 7640, 2364, 11175, 10833, 10858, 11178, 6457, 10859, 2470, 6311, 6161, 6385, 10134, 6272, 6319, 10708, 10164, 6307, 6312, 6421, 6431, 6377, 6163, 6387, 10712, 8093, 8281, 8102, 8090, 8216, 6389, 10132, 2479, 6380, 6314, 6317, 6258, 7639, 6261, 6382, 6259, 3693, 10777, 10733, 10736, 10778, 10173, 13149, 10124, 10166, 8667, 10189, 8668, 10140, 13148, 8673, 10177, 8710, 8715, 8671, 13151, 8676, 10163, 10168, 6454, 6162, 6453, 10855, 11186.......

And it goes on and on for all the products in that category when the root menu item is clicked. Does anyone know how I can get the category pages to only query the items on the page, and do so individually every time someone clicks "next page"? Thanks for your help in advance.

7
  • 2
    This is interesting, Do you have any custom modules installed that would cause all products to be loaded in a single request? E.g. some form of lazy loading or infinite scroll? Commented Jan 29, 2015 at 20:46
  • I did have a module for AJAX pagination within the category, however I've disabled it a while ago, but still plagued with the issue. Even with AJAX pagination enabled, isn't the whole point of AJAX to pull data as needed (ie. querying the next 24 items via AJAX when clicking next button)? So I presume this type of large query isn't normal on a stock Magento instance, even with large volumes of products?
    – digidash
    Commented Jan 29, 2015 at 22:10
  • You are correct. The point of AJAX is to load only the required resources at any one time reducing the overall time spent collecting data. However I have seen implementations that load all of the data and set display:none on a part of the container in HTML to appear to be loading more. I would try removing this module fully from your store so we can eliminate this as a possible cause for this issue. Commented Jan 29, 2015 at 22:12
  • So I've tried completely removing the AJAX layered nav/pagination plugin, but still getting the same issue. Below is the grid mode portion of my list.phtml design template: pastebin.com/MsBcVxL9 I've skimmed over it, but wasn't able to find anything that could be causing the issue. Any other ideas? Thanks for your help so far.
    – digidash
    Commented Jan 29, 2015 at 23:36
  • Also, in case you were interested, I was able to catch the query using mytop on our mysql node.
    – digidash
    Commented Jan 29, 2015 at 23:40

2 Answers 2

1

I just had the same problem with an exporter I wrote. Be careful with the method getAllIds() on Mage_Catalog_Model_Resource_Product_Collection I expected got get all getLoadedIds() instead I got ALL ids.

0

Still having this issue?

Have you checked if there are any observers listening to the event catalog_block_product_list_collection ?

This is fired in Mage_Catalog_Block_Product_List::_beforeToHtml method, and is a way for 3rd party extensions to potentially alter the product collection that was built, just before the html (your blocks) is generated.

grep all the installed extensions config.xml files for catalog_block_product_list_collection

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.