Index prefix optimisation

MySQL & MariaDB Percona Server for MySQL 8.0
1

I’m trying to test the feature documented here:
[URL=“Prefix Index Queries Optimization — Percona Server 8.0 Documentation”]https://www.percona.com/doc/percona-...imization.html[/URL]

But in the test cases I’ve made, it simply doesn’t apply:

mysql> SELECT VERSION();
+-----------+
| VERSION() |
+-----------+
| 8.0.16-7 |
+-----------+
1 row in set (0.00 sec)

mysql> CREATE TABLE product (
-> id INT UNSIGNED AUTO_INCREMENT,
-> code VARCHAR(20),
-> quantity INT UNSIGNED,
-> PRIMARY KEY (id),
-> INDEX idx_code_3_quantity (code(3), quantity)
-> ) ENGINE InnoDB;
Query OK, 0 rows affected (0.03 sec)

mysql> INSERT INTO product (code, quantity) VALUES
-> ('ABC-something', 3)
-> , ('ABC-nothing', 0)
-> , ('ABC-anything', 1)
-> , ('ZZZ-whoever', 5)
-> , ('KKK-wherever', 3)
-> ;
Query OK, 5 rows affected (0.01 sec)
Records: 5 Duplicates: 0 Warnings: 0

mysql> SHOW GLOBAL STATUS LIKE 'Innodb_secondary_index_triggered_cluster_reads%';
+--------------------------------------------------------+-------+
| Variable_name | Value |
+--------------------------------------------------------+-------+
| Innodb_secondary_index_triggered_cluster_reads | 5228 |
| Innodb_secondary_index_triggered_cluster_reads_avo ided | 0 |
+--------------------------------------------------------+-------+
2 rows in set (0.00 sec)

mysql> SELECT quantity
-> FROM product
-> WHERE code LIKE 'ABC%' AND quantity > 0
-> ;
+----------+
| quantity |
+----------+
| 1 |
| 3 |
+----------+
2 rows in set (0.00 sec)

mysql> SHOW GLOBAL STATUS LIKE 'Innodb_secondary_index_triggered_cluster_reads%';
+--------------------------------------------------------+-------+
| Variable_name | Value |
+--------------------------------------------------------+-------+
| Innodb_secondary_index_triggered_cluster_reads | 5231 |
| Innodb_secondary_index_triggered_cluster_reads_avo ided | 0 |
+--------------------------------------------------------+-------+
2 rows in set (0.00 sec)

It could be a bug but, since I wasn’t able to find a single case where this feature works, I assume that I’m missing something instead. Can anyone from Percona explain in which cases it works, and why it doesn’t work in this test?

2

Hey Federico thanks for asking, and sorry your response fell victim to our focus on Percona Live Europe

You can find more about that feature at this link, and you might read a few test cases with that commit in case that helps illustrate use cases? [url]https://github.com/percona/percona-server/commit/1eee538087f[/url]

Once you have checked that out (if you haven’t already) then let me know if you have follow up questions and I’ll see if I can get some follow up if needed. Hope this helps?

3

I think there is a mistake in my example, but that’s not the point, as I’ve tried many variants. This comment made me see where I was wrong:

/* ...but, perhaps avoid the clustered index lookup if
all of the following are true:
1) all columns are in the secondary index
2) all values for columns that are prefix-only
indexes are shorter than the prefix size
This optimization can avoid many IOs for certain schemas.
*/

Why does the value need to be shorter than prefix? I think that this optimisation would be more useful if it could work when the value has the same length as the prefix.

4

I’ll ask someone to take a look Federico, it may be we ask you to raise a Jira for this.