Caution -- this includes only the indexes you have actually used since turning on the stats.
> -----Original Message----- > From: Eric Bergen [mailto:eric.ber...@gmail.com] > Sent: Monday, October 15, 2012 8:55 PM > To: Lixun Peng > Cc: Perrin Harkins; Carlos Eduardo Caldi; mysql@lists.mysql.com > 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 <pengli...@gmail.com> > 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 <per...@elem.com> > wrote: > > > >> On Mon, Oct 15, 2012 at 11:24 AM, Carlos Eduardo Caldi > >> <ce_ca...@hotmail.com> 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.ber...@gmail.com > http://www.ebergen.net > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql
