There is a table of goods (goods more than 10 million), for each product a category key (catID) and a product serial number in this category (numInCat) are given:

CREATE TABLE `goods` ( `id` INT(9) UNSIGNED NOT NULL AUTO_INCREMENT, `catID` SMALLINT(2) UNSIGNED NOT NULL DEFAULT '0', `numInCat` MEDIUMINT(8) UNSIGNED NOT NULL DEFAULT '0', `name` VARCHAR(512) NULL DEFAULT NULL, # ... PRIMARY KEY (`id`), INDEX `catIDnumInCat` (`catID`, `numInCat`), ) ENGINE=InnoDB;

Record example:

 id=10352230 catID=67 numInCat=4936929 name='пакет с пупырышками' ...

You need to select 3 products from the category catID = 67 starting with the sequence number numInCat = 4936929 + 1 in order of numInCat:

 SELECT id,name FROM `goods` WHERE catID=67 AND numInCat>4936929 LIMIT 3

This request is executed in 1 second, which is very long for an online store. The operator EXPLAIN shows that the index catIDnumInCat is used, but 3086114 records are being searched! (key = catIDnumInCat, rows = 3086114)

If the request is changed to:

 SELECT id,name FROM `goods` WHERE catID=67 AND numInCat>4936929 AND numInCat<=4936929+3 LIMIT 3

Then it runs instantly. With this, according to the EXPLAIN operator, 3 entries are being moved.

But the query in this form is not suitable, since products with numInCat = 4936930,4936931,4936932 can simply be deleted earlier.

Q: Why does my first SELECT query iterate through so many rows in a database? Index catIDnumInCat because built and used.

To use ORDER BY catID, numInCat tried - to no avail. Yes, and this design if I make a mistake works after receiving the result.

  • it is easier to answer why the second request iterates over all three entries) - Ep1demic
  • Without an order by the way, the query does not guarantee at all what the next 3 entries will give, and not any that come up according to the condition. Look in both explain field key_len is it in both cases the same and equal to the total length of two fields in the index? PS In general, of course the behavior is very strange, you can try to make select id, name from ... where id in(select id from ... where catid.. numincat... order by ... limit 3) , sometimes this helps the optimizer - Mike
  • for the first request, key_len = 2 for the second 5 - Ruport
  • @Ruport That's what I was afraid of; in the first case, he decides to use only the first field from the index. And why, this is a mystery and here only one answer can be "this is MySQL ...". My proposal with the subquery does not help the same? - Mike
  • not. The subquery itself (in no way different from my first query, only by removing the name from the selection) enumerates 3086114 records. And LIMIT is not allowed in parentheses for IN - MySQL swears. - Ruport

0