Why is MySQL Innodb "Creating a sorting index" with a unique index?

Question

In a simple but very large Innodb table, I have a unique index in column A, and I want to get a list of (integer) column B in order of (integer) column A

Very simple query, I look through millions of records.

SELECT B FROM hugeTable ORDER BY A LIMIT 10000 OFFSET 500000

Does it take 10 seconds to request on a very fast server?

Filesort: Yes Filesort_on_disk: Yes Merge_passes: 9

That makes no sense to me, why can't he use Index A?

The explanation shows simple, without possible keys and filesort.

Answer 1

If the values ​​for column B are not available on the index pages, then MySQL must access the pages in the base table. There is also no predicate that filters which rows are considered, and this means that MySQL sees that ALL rows should be returned. This may explain why the index is not used.

Also note that LIMIT operations are processed at the end of the statement, as almost the last step in the execution plan, with some exceptions.

8.2.1.3. Query Optimization LIMIT http://dev.mysql.com/doc/refman/5.5/en/limit-optimization.html

I suspect your query might use a coverage index, such as " ON hugetable (A,B) ", to avoid a sort operation.

If there is no coverage index, you can try rewriting the query something like this to see if it will use the index in column A and avoid the sort operation by millions of rows (to get the first 510,000 rows returned in order):

 SELECT iB FROM ( SELECT jA FROM hugeTable j ORDER BY jA LIMIT 10000 OFFSET 500000 ) k JOIN hugetable i ON iA = kA ORDER BY kA 

I suggest you do EXPLAIN only to query the inline view (with an alias like k) and see if it shows " Using index ".

An external query will probably still have a “ Using filesort ” operation, but at least it will be only 10,000 lines.

(NOTE: You can try " ORDER BY iA " instead of " kA " in an external query and see if that changes.)

---

ADDITION

Not specifically considering your question, but in terms of the performance of this request, if it is "paging through" a set of lines, another option to consider in order to go to the "next" page is to use the value " A " from the last line received in the previous request , as the "starting point" for the next line.

The original request looks like this: "page 51" (10,000 lines per page, page 51 - lines 510 001 to 520,000).

If you also had to return the value "A" and save this for the last line. To get the “next” page, the query could really be:

  SELECT iB, kA FROM ( SELECT jA FROM hugeTable j WHERE jA > $value_of_A_from_row_520000 -- ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ LIMIT 10000 ) k JOIN hugetable i ON iA = kA ORDER BY kA 

If you also saved the value for A in the first line, you can use it to backup the page. It really only works to forward one page or back to one page. When jumping to another page, you will need to use the original request form, counting the lines.