MySQL Forums :: Newbie :: Slow query. Why is it slow?


Advanced Search

Slow query. Why is it slow?
Posted by: ben jaz ()
Date: May 19, 2013 06:35AM

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

Options: ReplyQuote


Subject Written By Posted
Slow query. Why is it slow? ben jaz 05/19/2013 06:35AM
Re: Slow query. Why is it slow? John Smith 05/19/2013 08:29AM
Re: Slow query. Why is it slow? Peter Brawley 05/19/2013 11:30AM
Re: Slow query. Why is it slow? Rick James 05/19/2013 06:27PM
Re: Slow query. Why is it slow? ben jaz 05/19/2013 06:34PM
Re: Slow query. Why is it slow? Rick James 05/19/2013 10:05PM
Re: Slow query. Why is it slow? ben jaz 05/19/2013 10:51PM
Re: Slow query. Why is it slow? Phillip Ward 05/20/2013 09:00AM
Re: Slow query. Why is it slow? Rick James 05/20/2013 11:20PM
Re: Slow query. Why is it slow? ben jaz 05/23/2013 06:21PM
Re: Slow query. Why is it slow? Rick James 05/23/2013 08:22PM
Re: Slow query. Why is it slow? ben jaz 05/26/2013 01:28AM
Re: Slow query. Why is it slow? Rick James 05/27/2013 10:02AM
Re: Slow query. Why is it slow? ben jaz 05/29/2013 04:47PM
Re: Slow query. Why is it slow? Rick James 05/29/2013 10:31PM
Re: Slow query. Why is it slow? ben jaz 06/02/2013 06:05AM
Re: Slow query. Why is it slow? Rick James 06/02/2013 12:55PM
Re: Slow query. Why is it slow? ben jaz 05/20/2013 12:01AM
Re: Slow query. Why is it slow? ben jaz 05/20/2013 05:48AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.