From: Eric Bergen
Date: October 16 2012 3:55am
Subject: Re: Can I measure the use of index?
List-Archive: http://lists.mysql.com/mysql/228404
Message-Id: <CAL8wyNzv6aA9fwOi5iZ1tPgWZVgadQpH6Vp_iXx9A9U9Ta7Grw@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1

For the record mariadb also has table and index statistics. Including
statistics on temporary tables.

On Mon, Oct 15, 2012 at 8:34 PM, Lixun Peng <penglixun@stripped> wrote:
> Hi,
>
> If you are using Percona Server, you can use this query:
>
> SELECT DISTINCT s.table_schema,
>                 s.table_name,
>                 s.index_name
> FROM   information_schema.statistics `s`
>        LEFT JOIN information_schema.index_statistics indxs
>          ON ( s.table_schema = indxs.table_schema
>               AND s.table_name = indxs.table_name
>               AND s.index_name = indxs.index_name )
> WHERE  indxs.table_schema IS NULL
>        AND s.index_name NOT IN ( 'PRIMARY' ) ;
>
> It will display all indexes that not use.
>
> or this query:
>
> SELECT table_name,
>        index_name,
>        SUM(rows_read)
> FROM   information_schema.index_statistics
> GROUP  BY table_name,
>           index_name;
>
> you can get the all indexes are using.
>
> On Tue, Oct 16, 2012 at 12:44 AM, Perrin Harkins <perrin@stripped> wrote:
>
>> On Mon, Oct 15, 2012 at 11:24 AM, Carlos Eduardo Caldi
>> <ce_caldi@stripped> wrote:
>> > Somebody knows how can I log or measure the index use ?
>>
>> http://www.percona.com/doc/percona-toolkit/2.1/pt-index-usage.html
>>
>> - Perrin
>>
>> --
>> MySQL General Mailing List
>> For list archives: http://lists.mysql.com/mysql
>> To unsubscribe:    http://lists.mysql.com/mysql
>>
>>
>
>
> --
> Senior MySQL Developer @ Taobao.com
>
> Mobile Phone: +86 18658156856 (Hangzhou)
> Gtalk: penglixun(at)gmail.com
> Twitter: http://www.twitter.com/plinux
> Blog: http://www.penglixun.com



-- 
Eric Bergen
eric.bergen@stripped
http://www.ebergen.net