List:General Discussion« Previous MessageNext Message »
From:Lixun Peng Date:October 16 2012 3:34am
Subject:Re: Can I measure the use of index?
View as plain text  
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

Thread
Can I measure the use of index?Carlos Eduardo Caldi15 Oct
  • Re: Can I measure the use of index?Reindl Harald15 Oct
    • RE: Can I measure the use of index?Carlos Eduardo Caldi15 Oct
  • Re: Can I measure the use of index?Perrin Harkins15 Oct
    • Re: Can I measure the use of index?Lixun Peng16 Oct
      • Re: Can I measure the use of index?Eric Bergen16 Oct
        • RE: Can I measure the use of index?Rick James16 Oct