Slow query. Why is it slow?
Hello I have 2 quesitons about the query below:
1 - It takes almost 20 seconds. How else can I optimise?
2 - Why not use convering_index_three?
Thank you
============================================================
SELECT `products_table`.`id`,
`code`,
`class`,
`category`,
`status`,
`price`,
`production_date`,
`products_status`.`title` AS STATUS
FROM products_table
JOIN `products_status`
ON `products_status`.`id` = `products_table`.`status`
WHERE `products_table`.`id` > 0
AND `class` = '6'
AND `category` = 'E'
ORDER BY `products_table`.`id`
LIMIT 50
================================
CREATE TABLE `products_table`
(
`id` INT(11) NOT NULL auto_increment,
`code` VARCHAR(4) NOT NULL,
`class` INT(11) NOT NULL,
`category` VARCHAR(5) NOT NULL,
`price` DECIMAL(6, 2) NOT NULL,
`production_date` DATE NOT NULL,
`quick_description` VARCHAR(255) NOT NULL,
`description` TEXT NOT NULL,
`status` INT(11) NOT NULL,
PRIMARY KEY (`id`),
KEY `production_date` (`production_date`),
KEY `index_price` (`price`),
KEY `class_index` (`class`),
KEY `category_index` (`category`),
KEY `status_index` (`status`),
KEY `covering_index_class` (`class`, `category`, `price`, `production_date`
, `status`),
KEY `covering_index` (`id`, `code`, `class`, `category`, `price`,
`production_date`, `status`),
KEY `covering_index_three` (`id`, `class`, `category`, `price`,
`production_date`, `status`),
KEY `covering_category` (`id`, `category`, `price`, `production_date`,
`status`)
)
engine=innodb
auto_increment=7642518
DEFAULT charset=latin1
=================================
Indexes:
http://webmoosh.com/aashghalduni/ax/indexes.png
EXPLAIN:
http://webmoosh.com/aashghalduni/ax/explain_sql.png