This patch adds several session-scoped status variables to the MySQL server. You can use the variables to compute the fragmentation of the InnoDB data scanned in the last query executed by your connection. You can compute the fragmentation for both table scans and index scans.

The variables are reset at the beginning of each query, so they are not cumulative over the life of your connection; instead, they measure only the most recent query. They are meant to be useful for simple queries that read from a single table or index. They measure leaf pages only.

This patch hasn't been ported to 5.1 and 5.5 series yet, it's only available in the 5.0 branch.

Counts the number of leaf pages that were not read contiguously during the last query. It does not count the first page, so it will be 0 for single-page scans. Because it measures leaf pages only and “branch pages” must be inserted for scans on large tables, it will always be nonzero for large tables.

Counts the bytes occupied by garbage (not used by records) in the leaf pages that were scanned during the last query. To make the implementation more efficient, it does not count the last page in the scan.

The index scan was only about 1% contiguous, which is what we would expect for a B-Tree index that is not built in index order. Insertions into the index have also caused a lot of garbage space in the pages: about 15% of the total space is used for garbage. This is also typical for random insertions into a B-Tree, because random insertions cause many page splits.

In contrast to the index scan, we see that the table is not very fragmented. The table scan's page accesses are more than 99% contiguous, and the pages scanned contain much less than 1% garbage space. This is typical for data that is loaded in primary key order, which does not cause page splits in the primary key.