A query plan uses loose index scan if “Using index for group-by” appears in the “Extra” column of the EXPLAIN output. In some plans though, “Using index for group-by (scanning)” appears. What does “(scanning)” mean and how is it different from the regular loose index scan?…
Category Archives: Internals
MySQL Server bootstrapping and dictionary initialization
In MySQL 8.0, we are making large changes to the way the MySQL server stores meta data with the introduction of our native data dictionary. As part of these improvements, we have also made changes to the way the server bootstraps.…
What Makes the MySQL Audit Plugin API Special?
Why Should I Be Reading This?
To better understand how the MySQL Server functions, how to monitor the relevant server events, and find out what’s new in MySQL 5.7.8.
What’s Special About the Audit Plugin API?
Picking the right API for your new plugin is probably the most important design decision a plugin author will need to make.…
The InnoDB Change Buffer
One of the challenges in storage engine design is random I/O during a write operation. In InnoDB, a table will have one clustered index and zero or more secondary indexes. Each of these indexes is a B-tree. When a record is inserted into a table, the record is first inserted into clustered index and then into each of the secondary indexes. …
Performance Impact of InnoDB Transaction Isolation Modes in MySQL 5.7
During the process of reviewing our server defaults for MySQL 5.7, we thought that it might be better to change the default transaction isolation level from REPEATABLE-READ to READ-COMMITTED (the default for PostgreSQL, Oracle, and SQL Server). After some benchmarking, however, it seems that we should stick with REPEATABLE-READ as the default for now.…
Extent Descriptor Page of InnoDB
Within the MySQL data directory, the InnoDB storage engine creates two types of files — the data files and the redo log files. Each data file (or ibd file) belongs to exactly one tablespace. Each tablespace is given a unique identifier called the space_id. …