Description:
This is a feature request for the equivalent of "fast full index scan" with InnoDB.
I am using the Oracle name and some details on "fast full index scan" are here:
https://asktom.oracle.com/pls/apex/f?p=100:11:0::NO::P11_QUESTION_ID:3193365100346233806
A fast full index scan scans an index in storage order rather than index order. The benefit is much better IO throughput. MySQL recently added hash join to improve support for analytics, but if table scans (scanning an InnoDB PK index) are slow then we need more features.
I noticed this while running the TSBS benchmark for MySQL (ported by me) and there is one query that needs a full table scan (depending on the indexes that were created). On my home server I get ~80MB/s of IO with InnoDB vs ~900MB/s with Postgres. For InnoDB that is ~5000 16kb page reads/s per iostat. Trying to tune the InnoDB read ahead options doesn't have a significant impact. The query is IO bound, so Postgres is ~20X faster for this query.
Many years ago we added something that was similar to fast full index scan, but to make logical backup faster. I am not sure that is the best way to implement this feature:
http://yoshinorimatsunobu.blogspot.com/2013/10/making-full-table-scan-10x-faster-in.html
How to repeat:
Do 'select * from BigTable WHERE ...'
Suggested fix:
See above
