Why does MySQL have to examine 499990 rows? The table has primary key index on id column.
ORDER BY id ASC
LIMIT 499980, 10
This obviously mean that I have made a mistake in my design. How should I improve the performance here? Did I make a mistake in my query? Did I add the wrong index?
CREATE TABLE `products_table`
`id` INT(11) NOT NULL auto_increment,
`code` VARCHAR(4) NOT NULL,
`class` VARCHAR(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`)
The query takes approximately 10 seconds. It is a pagination in case you wonder.