Есть запрос вида:
SELECT * , pd.name AS name, p.image, m.name AS manufacturer, ss.name AS stock
FROM oc_product p
LEFT JOIN oc_product_description pd ON ( p.product_id = pd.product_id )
LEFT JOIN oc_product_to_store p2s ON ( p.product_id = p2s.product_id )
LEFT JOIN oc_manufacturer m ON ( p.manufacturer_id = m.manufacturer_id )
LEFT JOIN oc_stock_status ss ON ( p.stock_status_id = ss.stock_status_id )
LEFT JOIN oc_product_to_category p2c ON ( p.product_id = p2c.product_id )
WHERE p.status = '1'
AND p.date_available <= NOW( )
AND pd.language_id = '1'
AND p2s.store_id = '0'
AND ss.language_id = '1'
AND p2c.category_id = '346'
ORDER BY LCASE( pd.name ) ASC
LIMIT 0 , 12
его EXPLAIN:id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE m system PRIMARY NULL NULL NULL 0 const row not found
1 SIMPLE pd ALL PRIMARY,product_id NULL NULL NULL 5121 Using where; Using temporary; Using filesort1 SIMPLE ss ALL PRIMARY NULL NULL NULL 3 Using where; Using join buffer
1 SIMPLE p2s eq_ref PRIMARY PRIMARY 8 udb64.pd.product_id,const 1 Using where; Using index
1 SIMPLE p eq_ref PRIMARY PRIMARY 4 udb64.pd.product_id 1 Using where
1 SIMPLE p2c eq_ref PRIMARY PRIMARY 8 udb64.pd.product_id,const 1 Using where; Using index
SHOW CREATE pd:CREATE TABLE `oc_product_description` (
`product_id` smallint(6) unsigned NOT NULL AUTO_INCREMENT,
`language_id` enum('1') NOT NULL DEFAULT '1',
`name` varchar(100) NOT NULL DEFAULT '',
`meta_description` varchar(80) DEFAULT NULL,
`description` text,
`mode` varchar(32) DEFAULT NULL,
`pow_cooling` varchar(16) DEFAULT NULL,
`pow_heating` varchar(16) DEFAULT NULL,
`noise_max` varchar(8) DEFAULT NULL,
`noise_min` varchar(8) DEFAULT NULL,
`area` varchar(8) DEFAULT NULL,
`pow_c` varchar(16) DEFAULT NULL,
`max_t` varchar(8) DEFAULT NULL,
PRIMARY KEY (`product_id`),
UNIQUE KEY `product_id` (`product_id`,`name`),
KEY `name` (`name`)
) ENGINE=MyISAM AUTO_INCREMENT=34864 DEFAULT CHARSET=cp1251
Выходит из pd выбирается 5121 строка без использования индексов, что сильно тормозит. Какие индексы лучше использовать?
