сейчас посмотрим почему и как решить

мне кажется, что mysql выбирает неоптимальный план запроса

analyze пишет что мог бы отдать данные быстрее
(no early end due to SQL_CALC_FOUND_ROWS)
но т.к. есть SQL_CALC_FOUND_ROWS то запрос выполняется дольше и сканирует все.

покажите ddl таблицы cscart_products
product_id это primary key?

CREATE TABLE cscart_products (
 product_id mediumint unsigned NOT NULL AUTO_INCREMENT,
 product_code varchar(64) NOT NULL DEFAULT '',
 product_type char(1) NOT NULL DEFAULT 'P',
 status char(1) NOT NULL DEFAULT 'A',
 company_id int unsigned NOT NULL DEFAULT '0',
 list_price decimal(12,2) NOT NULL DEFAULT '0.00',
 amount mediumint NOT NULL DEFAULT '0',
 weight decimal(13,3) NOT NULL DEFAULT '0.000',
 length mediumint unsigned NOT NULL DEFAULT '0',
 width mediumint unsigned NOT NULL DEFAULT '0',
 height mediumint unsigned NOT NULL DEFAULT '0',
 shipping_freight decimal(12,2) NOT NULL DEFAULT '0.00',
 low_avail_limit mediumint unsigned NOT NULL DEFAULT '0',
 timestamp int unsigned NOT NULL DEFAULT '0',
 updated_timestamp int unsigned NOT NULL DEFAULT '0',
 usergroup_ids varchar(255) NOT NULL DEFAULT '0',
 is_edp char(1) NOT NULL DEFAULT 'N',
 edp_shipping char(1) NOT NULL DEFAULT 'N',
 unlimited_download char(1) NOT NULL DEFAULT 'N',
 tracking char(1) NOT NULL DEFAULT 'B',
 free_shipping char(1) NOT NULL DEFAULT 'N',
 zero_price_action char(1) NOT NULL DEFAULT 'R',
 is_pbp char(1) NOT NULL DEFAULT 'N',
 is_op char(1) NOT NULL DEFAULT 'N',
 is_oper char(1) NOT NULL DEFAULT 'N',
 is_returnable char(1) NOT NULL DEFAULT 'Y',
 return_period int unsigned NOT NULL DEFAULT '10',
 avail_since int unsigned NOT NULL DEFAULT '0',
 out_of_stock_actions char(1) NOT NULL DEFAULT 'N',
 localization varchar(255) NOT NULL DEFAULT '',
 min_qty smallint NOT NULL DEFAULT '0',
 max_qty smallint NOT NULL DEFAULT '0',
 qty_step smallint NOT NULL DEFAULT '0',
 list_qty_count smallint NOT NULL DEFAULT '0',
 tax_ids varchar(255) NOT NULL DEFAULT '',
 age_verification char(1) NOT NULL DEFAULT 'N',
 age_limit tinyint NOT NULL DEFAULT '0',
 options_type char(1) NOT NULL DEFAULT 'P',
 exceptions_type char(1) NOT NULL DEFAULT 'F',
 details_layout varchar(50) NOT NULL DEFAULT '',
 shipping_params varchar(255) NOT NULL DEFAULT '',
 facebook_obj_type varchar(64) NOT NULL,
 parent_product_id int unsigned NOT NULL DEFAULT '0',
 yml2_brand varchar(96) NOT NULL,
 yml2_origin_country varchar(64) NOT NULL,
 yml2_store char(1) NOT NULL DEFAULT '',
 yml2_pickup char(1) NOT NULL DEFAULT '',
 yml2_delivery char(1) NOT NULL DEFAULT '',
 yml2_adult char(1) NOT NULL DEFAULT 'N',
 yml2_delivery_options text,
 yml2_bid smallint unsigned NOT NULL DEFAULT '0',
 yml2_cbid smallint unsigned NOT NULL DEFAULT '0',
 yml2_fee smallint unsigned NOT NULL DEFAULT '0',
 yml2_model varchar(96) NOT NULL DEFAULT '',
 yml2_sales_notes varchar(50) NOT NULL DEFAULT '',
 yml2_type_prefix varchar(55) NOT NULL DEFAULT '',
 yml2_offer_type varchar(32) NOT NULL DEFAULT '',
 yml2_market_category varchar(255) NOT NULL DEFAULT '',
 yml2_manufacturer_warranty char(1) NOT NULL DEFAULT '',
 yml2_expiry varchar(20) NOT NULL DEFAULT '',
 yml2_purchase_price int NOT NULL DEFAULT '0',
 yml2_description text,
 yml2_cpa char(1) NOT NULL DEFAULT 'Y',
 buy_now_url varchar(255) NOT NULL,
 external_id varchar(128) NOT NULL DEFAULT '',
 update_1c char(1) NOT NULL DEFAULT 'Y',
 delivery_cat_id int NOT NULL DEFAULT '0',
 qty_store mediumint DEFAULT NULL,
 qty_virt mediumint DEFAULT NULL,
 qty_east mediumint DEFAULT NULL,
 qty_west mediumint DEFAULT NULL,
 PRIMARY KEY (product_id),
 KEY age_verification (age_verification,age_limit),
 KEY status (status),
 KEY idx_parent_product_id (parent_product_id),
 KEY idx_find_by_code (product_code,company_id) USING BTREE,
 KEY idx_product_code (product_code) USING BTREE,
 KEY idx_usergrp_ids (usergroup_ids,status,parent_product_id)
) ENGINE=InnoDB AUTO_INCREMENT=506897 DEFAULT CHARSET=utf8

начнем с мелочи. buffer poolsize настроен?

покажите DDL еще cscart_products_categories

innodb_buffer_pool_size = 2G

увеличте до 6.

Вторая:

CREATE TABLE cscart_products_categories (
 product_id mediumint unsigned NOT NULL DEFAULT '0',
 category_id mediumint unsigned NOT NULL DEFAULT '0',
 link_type char(1) NOT NULL DEFAULT 'M',
 position smallint unsigned NOT NULL DEFAULT '0',
 category_position smallint unsigned NOT NULL DEFAULT '0',
 PRIMARY KEY (category_id,product_id),
 KEY link_type (link_type),
 KEY pt (product_id,link_type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

ваш запрос используется на сайте?

или это что то аналитическое?

Мой - нет, это сииильно урезанная версия запроса, который выполняется 7-10 сек

так там еще where есть?

Страница открывается в сумме почти 1 минуту

да =(

# Query_time: 9.062142  Lock_time: 0.000632  Rows_sent: 0  Rows_examined: 1280210  Rows_affected: 0

Это из слоуЛога

мы оптимизируем это или запрос с where. с where это совсем другое

если это, то тут в вашем случае, когда один товар одна категория, на  индекс pt надо вешать unique?