Not the answer you need?
Register and ask your own question!

Index prefix optimisation

Federico RazzoliFederico Razzoli ContributorCurrent User Role Patron
I'm trying to test the feature documented here:
https://www.percona.com/doc/percona-...imization.html

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?

Comments

  • lorraine.pocklingtonlorraine.pocklington Percona Community Manager Legacy User Role Patron
    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? https://github.com/percona/percona-server/commit/1eee538087f

    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?
  • Federico RazzoliFederico Razzoli Contributor Current User Role Patron
    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.
  • lorraine.pocklingtonlorraine.pocklington Percona Community Manager Legacy User Role Patron
    I'll ask someone to take a look Federico, it may be we ask you to raise a Jira for this.
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.