List:General Discussion« Previous MessageNext Message »
From:Rick James Date:October 16 2012 8:05pm
Subject:RE: Can I measure the use of index?
View as plain text  
Caution -- this includes only the indexes you have actually used since turning on the
stats.


> -----Original Message-----
> From: Eric Bergen [mailto:eric.bergen@stripped]
> Sent: Monday, October 15, 2012 8:55 PM
> To: Lixun Peng
> Cc: Perrin Harkins; Carlos Eduardo Caldi; mysql@stripped
> Subject: Re: Can I measure the use of index?
> 
> 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
> 
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:    http://lists.mysql.com/mysql

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