Userstats to capture how many bytes of an index has been used

[Ryan Lowe]

Say I have the following table:

CREATE TABLE `t1` (
  `id` int unsigned NOT NULL auto_increment PRIMARY KEY,
  `c1` int,
  `c2` int,
  `c3` int,
  INDEX `idx1` (`c3`,`c2`,`c1`)
  );

And I issue the following query:

SELECT `id` FROM `t1` WHERE `c3`=6;

This will show that the index has been used, but what I want to know is how many bytes were used. This way I can not only drop unused indexes, but also shorten indexes that include too many columns.

[Yasufumi Kinoshita (yasufumi-kinoshita)]

Sorry, I don't catch up with your intention of "how many bytes were used" of index....

Do you want each read/write access to the index pages in buffer pool?

e.g.)
root page: scan page_no to find page_no of the leaf pages 8+8+8+...
branch page: scan page_no to find page_no of the next pages 8+8+8+...
....
leaf page: scan rec and read it 8+8+....40

I don't want such slower and big patch to InnoDB/MyISAM.....
And I don't think "how many bytes were used" are useful...

[Ryan Lowe (ryan-a-lowe)]

Hey Yasufumi,

Say I have:

mysql> SHOW CREATE TABLE t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `c1` int(11) default NULL,
  `c2` int(11) default NULL,
  `c3` int(11) default NULL,
  `c4` int(11) default NULL,
  `c5` int(11) default NULL,
  KEY `c1` (`c1`,`c2`),
  KEY `c2` (`c2`,`c3`,`c4`,`c5`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> EXPLAIN SELECT COUNT(*) FROM t1 WHERE c1=1234\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
         type: ref
possible_keys: c1
          key: c1
      key_len: 5
          ref: const
         rows: 1
        Extra: Using where; Using index
1 row in set (0.00 sec)

mysql> EXPLAIN SELECT COUNT(*) FROM t1 WHERE c1=1234 AND c2=12345\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
         type: ref
possible_keys: c1,c2
          key: c1
      key_len: 10
          ref: const,const
         rows: 1
        Extra: Using where; Using index
1 row in set (0.00 sec)

Note the key_len goes from 5 to 10. What happens is that I often see customers who have indexes on (c1,c2,c3,c4,...,c15) but when I do a query analysis, I only see queries with WHERE c1=x and c2=y. They know they added the index in the past for a reason, but can't remember why. I guess one option is for me to find all queries that access the table and then manually EXPLAIN them to see how much of the index they use, but that seems like a lot of work.

There's no way of tracking this without significant performance impact?

[Vadim Tkachenko (vadim-tk)]

This is not easy to implement, I moved it to "Wishlist"

[Stewart Smith (stewart)]

Moved to Percona Server

[Shahriyar Rzayev (rzayev-sehriyar)]

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PS-2295