В магазине 8000 товаров. около 70 категорий. При выводе 20 товаров в категории генерируется 350-420 запросов. При просмотре товара генерируется около 60 запросов.
Методом научного втыка были проведены следующие изменения.
ps_perm.php
Запрос
строка 35
$db->query('SELECT group_id,group_name,group_level FROM `#__{vm}_auth_group` ORDER BY group_level');
ps_product.php
строка 654-656
$q = "SELECT `product_type_id` FROM `#__{vm}_product_product_type_xref` WHERE ";
$q .= "`product_id`=$product_id";
$db->query($q);
строка 823
$db->query( "SELECT file_id FROM #__{vm}_product_files WHERE file_product_id='$product_id'" );
строка 1033-1035
$q = "SELECT COUNT(product_id) as num_rows FROM #__{vm}_product WHERE product_parent_id='$pid' ";
$db->query($q);
$db->next_record();
строка 1148-1153
$q = "SELECT #__{vm}_vendor.vendor_name FROM #__{vm}_product, #__{vm}_vendor ";
$q .= "WHERE #__{vm}_product.product_id='$product_id' ";
$q .= "AND #__{vm}_vendor.vendor_id=#__{vm}_product.vendor_id";
$db->query($q);
$db->next_record();
строка 1217-1221
$q = "SELECT manufacturer_id FROM #__{vm}_product_mf_xref ";
$q .= "WHERE product_id='$product_id' ";
$db->query($q);
$db->next_record();
строка 1239-1244
$q = "SELECT mf_name,#__{vm}_manufacturer.manufacturer_id FROM #__{vm}_product_mf_xref,#__{vm}_manufacturer ";
$q .= "WHERE product_id='$product_id' ";
$q .= "AND #__{vm}_manufacturer.manufacturer_id=#__{vm}_product_mf_xref.manufacturer_id";
$db->query($q);
$db->next_record();
строка 1577-1579
$q = 'SELECT tax_rate FROM #__{vm}_product, #__{vm}_tax_rate ';
$q .= 'WHERE product_tax_id=tax_rate_id AND product_id='.(int)$product_id;
$db->query($q);
строка 2324-2327
$q = "SELECT amount,is_percent FROM #__{vm}_product,#__{vm}_product_discount ";
$q .= "WHERE product_id='$product_id' AND (start_date<='$starttime' OR start_date=0) AND (end_date>='$endofday' OR end_date=0) ";
$q .= "AND product_discount_id=discount_id";
$db->query( $q );
строка 2339-2340
$q = "SELECT product_parent_id FROM #__{vm}_product WHERE product_id=$product_id";
$db->query($q);
строка 2342-2345
$q = "SELECT amount,is_percent FROM #__{vm}_product,#__{vm}_product_discount ";
$q .= "WHERE product_id='".$db->f("product_parent_id")."' AND (start_date<='$starttime' OR start_date=0) AND (end_date>='$endofday' OR end_date=0) ";
$q .= "AND product_discount_id=discount_id";
db->query( $q );
$q .= "(#__{vm}_product.product_parent_id='' OR #__{vm}_product.product_parent_id='0') \n";
строка 2763-2772
$q = "SELECT product_name, category_name, c.category_flypage,product_s_desc,product_thumb_image ";
$q .= "FROM #__{vm}_product as p,#__{vm}_category as c,#__{vm}_product_category_xref as cx ";
$q .= "WHERE p.product_id = '$prod_id' ";
$q .= "AND c.category_id = '$category_id' ";
$q .= "AND p.product_id = cx.product_id ";
$q .= "AND c.category_id=cx.category_id ";
$q .= "AND p.product_publish='Y' ";
$q .= "AND c.category_publish='Y' ";
$q .= "LIMIT 0,1";
$db->query( $q );
файл ps_product_attribute.php
строка 263-264
$q = "SELECT product_id,product_name FROM #__{vm}_product WHERE product_parent_id='$product_id' AND product_publish='Y'" ;
$db->query($q.$q1);
строка 720-722
$q = "SELECT product_id, attribute, product_parent_id FROM #__{vm}_product WHERE product_id='$product_id'";
$db->query($q);
$db->next_record();
строка 725-727
$q = "SELECT product_id, attribute FROM #__{vm}_product WHERE product_id='$parent_id'";
$db->query($q);
$db->next_record();
строка 809-811
$q = "SELECT product_id, custom_attribute, product_parent_id FROM #__{vm}_product WHERE product_id='$product_id'";
$db->query($q);
$db->next_record();
строка 814-816
$q = "SELECT product_id, custom_attribute FROM #__{vm}_product WHERE product_id='$parent_id'";
$db->query($q);
$db->next_record();
файл ps_product_category.php
строка 926-933
$q = "SELECT category_id, category_thumb_image, category_child_id,category_name FROM #__{vm}_category,#__{vm}_category_xref ";
$q .= "WHERE #__{vm}_category_xref.category_parent_id='$category_id' ";
$q .= "AND #__{vm}_category.category_id=#__{vm}_category_xref.category_child_id ";
$q .= "AND #__{vm}_category.vendor_id='$ps_vendor_id' ";
$q .= "AND #__{vm}_category.category_publish='Y' ";
$q .= "ORDER BY #__{vm}_category.list_order, #__{vm}_category.category_name ASC";
$db->setQuery($q);
$db->query();
строка 1202-1206
$q = "SELECT category_id, category_description FROM #__{vm}_category ";
$q .= "WHERE category_id='$category_id' ";
$db->setQuery($q); $db->query();
$db->next_record();
строка 1274-1278
$q = "SELECT category_id, category_name,category_parent_id FROM #__{vm}_category, #__{vm}_category_xref WHERE ";
$q .= "#__{vm}_category_xref.category_child_id='$category_id' ";
$q .= "AND #__{vm}_category.category_id='$category_id'";
$db->setQuery($q); $db->query();
$db->next_record();
файл product.file_list.php
строка 604-611
$sql = 'SELECT DISTINCT file_id, file_mimetype, file_title, file_name'
. ' FROM `#__{vm}_product_files` WHERE ';
if( $exclude_filename ) {
$sql .= ' file_title != \''.$exclude_filename.'\' AND ';
}
$sql .= 'file_product_id = \''.$product_id.'\' AND file_published = \'1\' AND file_is_image = \'0\'';
$dbf->setQuery($sql);
$dbf->query();
строка 682
$db->query( "SELECT * FROM `#__{vm}_product_files` WHERE `file_product_id`=".intval($pid)." AND `file_is_image`=1 AND `file_published`=1" );
строка 684-685
$db->query("SELECT product_parent_id FROM #__{vm}_product WHERE product_id=".intval($pid) );
$db->query( "SELECT * FROM `#__{vm}_product_files` WHERE `file_product_id`=".$db->f("product_parent_id")." AND `file_is_image`=1 AND `file_published`=1" );
строка 693
$db->query( "SELECT * FROM `#__{vm}_product_files` WHERE `file_product_id`=".intval($pid)." AND `file_is_image`=0 AND `file_published`=1" );
строка 695
$db->query("SELECT product_parent_id FROM #__{vm}_product WHERE product_id='$pid'");
строка 698
$db->query( "SELECT * FROM `#__{vm}_product_files` WHERE `file_product_id`=".$db->f("product_parent_id")." AND `file_is_image`=0 AND `file_published`=1" );
файл ps_product_type.php
строка 514-515
$q = "SELECT * FROM #__{vm}_product_product_type_xref WHERE product_id='$product_id'";
$db->query($q);
файл ps_shopper_group.php
строка 372-375
$q = "SELECT shopper_group_id,shopper_group_discount FROM #__{vm}_shopper_group WHERE ";
$q .= "vendor_id='$vendor_id' AND `default`='1'";
$db->query( $q );
$db->next_record();
файл ps_vendor.php
заменить
строка 441-444
$q = "SELECT vendor_id, vendor_min_pov,vendor_name,vendor_store_name,contact_email,vendor_full_image, vendor_freeshipping,
vendor_address_1,vendor_address_2, vendor_url, vendor_city, vendor_state, vendor_country, country_2_code, country_3_code,
vendor_zip, vendor_phone, vendor_store_desc, vendor_currency, vendor_currency_display_style,
vendor_accepted_currencies, vendor_address_format, vendor_date_format, state_name
$q = "SELECT vendor_id, vendor_currency, vendor_currency_display_style
файл shop.product_details.php
строка 83-86
$q = "SELECT product_sku, related_products FROM #__{vm}_product,#__{vm}_product_relations ";
$q .= "WHERE #__{vm}_product_relations.product_id='$product_id' AND product_publish='Y' ";
$q .= "AND FIND_IN_SET(#__{vm}_product.product_id, REPLACE(related_products, '|', ',' )) LIMIT 0, 4";
$db->query( $q );
файл shop_browse_queries.php
заменить строка 37-39
$fieldnames = "`product_name`,`products_per_row`,`category_browsepage`,`category_flypage`,`#__{vm}_category`.`category_id`,
`#__{vm}_product`.`product_id`,`product_full_image`,`product_thumb_image`,`product_s_desc`,`product_parent_id`,`product_publish`,`product_in_stock`,`product_sku`,`author`,`location`,`publisher`,`god`,`pages`,`isbn`,`format`,`savepages`,`nomer`,`tip`,`product_url`,
`product_weight`,`product_weight_uom`,`product_length`,`product_width`,`product_height`,`product_lwh_uom`,`product_in_stock`,`product_available_date`,`product_availability`,`#__{vm}_product`.`mdate`, `#__{vm}_product`.`cdate`";
на
$fieldnames = "`product_name`,`products_per_row`,`category_browsepage`,`category_flypage`,
`#__{vm}_product`.`product_id`,`product_s_desc`,`product_sku`,`author`,`location`,`publisher`,`god`,`pages`,`isbn`,`format`,`savepages`,`nomer`,`tip`
";
В результате:
Категории - сократились с 350-420 до 120
Просмотр товара - с 60 до 30
Все удаленные запросы лично мне не были нужны, возможно кому-то пригодится.
http://www.onlinedisk.ru/file/363934/
КОЛОСАЛЬНО! После проделаной работы у меня запросы на странице отображения товаров (50 штук) снизились с 1021 до 427! Спасибо большое soter!
P.S. Мне не удалось найти у себя файлик shop.product_details.php. Подскажите, где он может находится?
26.07.2021, 22:11:25