Why does MySQL have to examine 499990 rows? The table has primary key index on id column.
Code:
SELECT id,
       code
FROM   products_table
ORDER  BY id ASC
LIMIT  499980, 10
Code:
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`)
  )
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?
The query takes approximately 10 seconds. It is a pagination in case you wonder.

Thank you