#1
  1. A Change of Season
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2004
    Location
    Next Door
    Posts
    2,650
    Rep Power
    171

    Why doesn Mysql scan too many unneccessary rows?


    Why does it scan that many rows when it only needs 30 rows?

    Code:
    SELECT id
    FROM   products_table
    WHERE  products_table.id > 1
    ORDER  BY id
    LIMIT  30
    EXPLAIN
    1 SIMPLE products_table range PRIMARY,covering_index covering_index 4 NULL 2363932 Using where; Using index
    Why did it use covering index and not PRIMARY KEY????!!!!!!!
    Code:
     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`)
      )
    engine=innodb
    auto_increment=7142518
    DEFAULT charset=latin1
    Last edited by zxcvbnm; May 15th, 2013 at 05:59 AM.
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2012
    Posts
    65
    Rep Power
    3
    Originally Posted by zxcvbnm
    Why does it scan that many rows when it only needs 30 rows?

    Code:
    SELECT id
    FROM   products_table
    WHERE  products_table.id > 1
    ORDER  BY id
    LIMIT  30
    EXPLAINWhy did it use covering index and not PRIMARY KEY????!!!!!!!
    Code:
     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`)
      )
    engine=innodb
    auto_increment=7142518
    DEFAULT charset=latin1
    i'm not positive, but i'm pretty sure it's because your covering_index KEY contains the primary key
  4. #3
  5. A Change of Season
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2004
    Location
    Next Door
    Posts
    2,650
    Rep Power
    171
    Originally Posted by bobert123
    i'm not positive, but i'm pretty sure it's because your covering_index KEY contains the primary key
    Really? Why? What does it have anything to do with anything? I got this from Mysql forum
    In your particular case, it will touch only 30 rows. The 2363932 is bogus; EXPLAIN does not have the smarts to figure out that the index starts with the field(s) of the ORDER BY, and that the WHERE clause is consumed getting the query started (that is, is not used for filtering out rows). Furthermore, "Using index" says that the query looked _only_ at the index, and did not have to touch the data. You can't get much better than all of that!
  6. #4
  7. No Profile Picture
    Problem Solver
    Devshed Specialist (4000 - 4499 posts)

    Join Date
    Jan 2001
    Location
    Stockholm, Sweden
    Posts
    4,495
    Rep Power
    534
    Did you try the performance?

    The ORDER BY ... LIMIT optimization in MySQL is notorious about returning false information about rows touched. Basically it only returns rows estimated on the WHERE and does not consider the LIMIT.

    The reason why it uses the covering_index is probably because that index will be smaller in size (MB) than the primary key index. Which sounds odd but the reason is that all data in an InnoDB table is actually stored in the primary key index. Oracle call this index-organized table. This is great if you want to access row data based on the primary key where you avoid extra seeks. But if you have a query that only involves the primary key column you would probably be better off by creating a separate secondary index for the primary key column since that would be really small since it only contains that one column.
    And in this case I think MySQL chooses between the lesser of two evils, eg two large indexes, when it decides which to use.

    But as I said check the performance by timing the query because I think it should be fast although the EXPLAIN wrongly reports a lot of rows.
    /Stefan
  8. #5
  9. A Change of Season
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2004
    Location
    Next Door
    Posts
    2,650
    Rep Power
    171
    Originally Posted by sr
    Did you try the performance?
    Yes it runs pretty fast (0.0002 seconds).
    Originally Posted by sr
    The ORDER BY ... LIMIT optimization in MySQL is notorious about returning false information about rows touched. Basically it only returns rows estimated on the WHERE and does not consider the LIMIT.
    How inconvenient! This is the usage of EXPLAIN that I need and it is not useful to me ! I keep having the same issue in diffferent places as well.

    Thanks

IMN logo majestic logo threadwatch logo seochat tools logo