baltun
Администратор
- Сообщения
- 10.872
- Реакции
- 2.584
- Баллы
- 2.003
Делюсь индексами возможно не так оптимизированными с точки зрения базы данных, но тем не менее они помогли снять нагрузку с ЦП которая достигала 4х100% при обмене данными с 1С до банальных 5-10% и при этом сам скорость работы в панели администратора увеличилась в разы с товаром в 5к, да это не большое количество но раньше наблюдались существенные тормоза.
Для примера добавить свой индекс можно так:
Удалить не нужный индекс можно так:
Индексы не универсальные подойдут для темы Revolution 5.2.1 модуля NeoSeo Обмен с 1C.
Код:
create index category_filter_id ON oc_category_filter(filter_id);
create index product_filter_id ON oc_product_filter(filter_id);
create index product_filter_pr ON oc_product_filter(filter_id, product_id);
create index product_to_category_id ON oc_product_to_category(product_id);
create index product_to_category_rr ON oc_product_to_category(category_id, main_category);
create index filter_group_inx_id ON oc_filter_description(filter_group_id);
create index language_inx_id ON oc_filter_description(language_id);
create index filter_inx_id ON oc_filter_description(filter_id);
create index name_inx_id ON oc_filter_description(name);
create index nmind_inx ON oc_filter_description (language_id, filter_id, name);
create index nmind_group_inx ON oc_filter_description (language_id, filter_group_id, name);
create index nmind_group_inxx ON oc_filter_description (language_id, name);
create index filter_inx ON oc_filter (filter_id);
create index filter_group_inx ON oc_filter (filter_group_id);
create index filter_group_des ON oc_filter_group_description (language_id, name);
create index filter_group_dess ON oc_filter_group_description (language_id, filter_group_id, name);
create index product_xx ON oc_product(model, sku, product_id, manufacturer_id);
create index product_xi ON oc_product(product_id, price);
create index product_pr ON oc_product(price);
create index product_ii ON oc_product(product_id);
create index product_iаа ON oc_product(sort_order, model, quantity, price, date_added);
create index product_iа ON oc_product(quantity);
create index product_dat ON oc_product(date_added);
create index product_datt ON oc_product(date_available);
create index product_st ON oc_product(status);
create index sr_pr ON oc_product(sort_order);
create index sr_nm ON oc_product(name);
create index weight_pr ON oc_product(weight_class_id);
create index review_ii ON oc_review(product_id);
create index review_gg ON oc_review(status);
create index review_ff ON oc_review(product_id, status, date_added);
create index discount_ii ON oc_product_discount(product_id);
create index discount_iff ON oc_product_discount(customer_group_id, quantity);
create index discount_ife ON oc_product_discount(date_end);
create index translation_xx ON oc_product_discount (date_start);
create index discount_pp ON oc_product_discount (priority, price);
create index special_ii ON oc_product_special(product_id);
create index special_ss ON oc_product_special(customer_group_id, date_start, date_end, priority, price);
create index special_pp ON oc_product_special(priority, price);
create index to_store_ii ON oc_product_to_store(product_id);
create index store_id ON oc_product_to_store(store_id);
create index reward_gg ON oc_product_reward(customer_group_id);
create index reward_ ON oc_product_reward(product_id);
create index image_ii ON oc_product_image(product_id, sort_order);
create index image_ord ON oc_product_image(sort_order);
create index product_description_xi ON oc_product_description(product_id, language_id, name);
create index product_description_ii ON oc_product_description(product_id);
create index product_description_iix ON oc_product_description(name);
create index product_description_iixx ON oc_product_description(language_id);
create index order_xi ON oc_order(order_status_id);
create index attribute_xx ON oc_product_attribute (product_id, attribute_id, language_id);
create index setting_xx ON oc_setting (store_id);
create index setting_xi ON oc_setting (code);
create index attribute_xi ON oc_attribute (attribute_id);
create index attributed_xx ON oc_attribute_description (attribute_id, language_id, name);
create index category_xx ON oc_category (category_id);
create index category_description_xx ON oc_category_description (category_id);
create index to_store_xx ON oc_category_to_store (category_id);
create index translation_xx ON oc_theme (store_id, theme, route);
create index layout_xx ON oc_layout_route (route);
create index layout_xx ON oc_manufacturer (manufacturer_id, name);
create index man_nm ON oc_manufacturer (name);
create index layout_ii ON oc_manufacturer (manufacturer_id);
create index length_tt ON oc_length_class_description (unit, length_class_id, language_id);
create index length_id ON oc_length_class_description (length_class_id);
create index stock_tt ON oc_stock_status (name, stock_status_id, language_id);
create index stock_id ON oc_stock_status (stock_status_id);
create index stock_nn ON oc_stock_status (name);
create index weight ON oc_weight_class_description (unit, weight_class_id, language_id);
create index weight_id ON oc_weight_class_description (weight_class_id);
create index weight ON oc_weight_class (weight_class_id);
create index warehouse_xx ON oc_product_warehouse (product_id, warehouse_id, quantity);
create index warehouse_xi ON oc_product_warehouse (warehouse_id);
create index warehouse_xid ON oc_product_warehouse (product_id);
create index warehouse_xqq ON oc_product_warehouse (quantity);
create index warehouse_ie ON oc_warehouse (warehouse_id, name, sort_order);
create index warehouse_id ON oc_warehouse (warehouse_id);
create index warehouse_ord ON oc_warehouse (sort_order);
create index warehouse_wd ON oc_warehouse_description (warehouse_id, language_id);
create index warehouse_pp ON oc_warehouse_description (param1, param2, param3);
create index warehouse_ppp ON oc_warehouse_description (param4, param5);
create index warehouse_des_id ON oc_warehouse_description (warehouse_id);
create index warehouse_wd ON oc_warehouse_to_store (warehouse_id);
create index to_1c_id ON oc_product_to_1c (1c_id);
create index tax_wd ON oc_tax_rate (tax_rate_id, name, rate);
create index tax_odd ON oc_tax_rate (geo_zone_id);
create index tax_wd ON oc_tax_rule (tax_class_id, priority);
create index tax_yy ON oc_tax_rule (priority);
create index tax_wt ON oc_tax_rule (tax_rate_id);
create index tax_wrr ON oc_tax_rate (tax_rate_id);
create index tax_yy ON oc_tax_rate_to_customer_group (tax_rate_id);
create index cart_uu ON oc_cart (api_id, customer_id, session_id);
create index banner_tt ON oc_banner (banner_id, status);
create index banner_bm ON oc_banner_image (banner_id, language_id, sort_order);
create index banner_lm ON oc_layout_module (layout_id, position, sort_order);
create index information_lm ON oc_information (information_id, status, sort_order);
create index information_lm ON oc_information_description (information_id, language_id, title);
create index information_lm ON oc_information_to_store (information_id, store_id);
create index zone_to_geo_lm ON oc_zone_to_geo_zone (geo_zone_id);
create index session_id ON oc_session (session_id, expire);Для примера добавить свой индекс можно так:
Код:
ALTER TABLE oc_product ADD INDEX product_xx (model, sku, product_id, manufacturer_id);Удалить не нужный индекс можно так:
Код:
DROP INDEX product_filter_id ON oc_product_filter;
Последнее редактирование:
