From: Rick James
Date: October 16 2012 8:05pm
Subject: RE: Can I measure the use of index?
List-Archive: http://lists.mysql.com/mysql/228417
Message-Id: <2E7DD7ADE53B044C8C8BCD9C5829E1EB148D27C0F5@SP2-EX07VS01.ds.corp.yahoo.com>
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

Caution -- this includes only the indexes you have actually used since turn=
ing 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?
>=20
> For the record mariadb also has table and index statistics. Including
> statistics on temporary tables.
>=20
> 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 =3D indxs.table_schema
> >               AND s.table_name =3D indxs.table_name
> >               AND s.index_name =3D 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
>=20
>=20
>=20
> --
> Eric Bergen
> eric.bergen@stripped
> http://www.ebergen.net
>=20
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:    http://lists.mysql.com/mysql