Покажи ддл таблиц

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

Вторая:

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

Ну так ни хера у тебя таблица

А джойн покажи

Да это пздц. Надо Эксель ))

Это CMS, это не я =)))

Вот Джойн:
SELECT
 SQL_CALC_FOUND_ROWS
 products.product_id,
 products.product_type,
 products.parent_product_id
FROM
 cscart_products as products
INNER JOIN
 cscart_products_categories as products_categories
   ON products_categories.product_id = products.product_id
GROUP BY products.product_id
ORDER BY products.product_id ASC
LIMIT 0, 10;

Какая CMS?

CS-Cart

GROUP BY products.product_id

Зачем?

Не делай иннер джойн просто и будут уникальные id товара

Тем более зачем вообще там джойн, если ты ни одного поля категорий не юзаешь?

С какой целью тут джойн?

Глобально ситуация следующая:
Я установил CMS из коробки. запустил на тестовых данных и всё было хорошо. Сделал импорт текущего боевого сайта (Другая CMS - самописка). Там примерно 200 твс товаров. И вот тут всё просело... Сначала было открытие карточки товара 5-7 минут. Потом я настроил индексы, перевел таблицы из MyIsam в InnoDB и вот сейчас один запрос выполняется уже 7-10 секунд, а страница товара открывается около 40 секунд, но иногда до 1 минуты дотягивает

А как делал импорт?

CMSки как правило не рассчитаны на такие количества

А кто составлял запрос? CMS?

написал скриптик портирования и залил из одной БД в другую

Тот, который я выше прислал - это сииильно урезанная версия запроса из CMS