RE: Can I measure the use of index?
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 > 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 > wrote: > > > >> On Mon, Oct 15, 2012 at 11:24 AM, Carlos Eduardo Caldi > >> 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
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 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 wrote: > >> On Mon, Oct 15, 2012 at 11:24 AM, Carlos Eduardo Caldi >> 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
Re: Can I measure the use of index?
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 wrote: > On Mon, Oct 15, 2012 at 11:24 AM, Carlos Eduardo Caldi > 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
Re: Can I measure the use of index?
On Mon, Oct 15, 2012 at 11:24 AM, Carlos Eduardo Caldi 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
RE: Can I measure the use of index?
I want to count how many time one index was used during a day, do you now how to log it to count? > Date: Mon, 15 Oct 2012 17:27:54 +0200 > From: h.rei...@thelounge.net > To: mysql@lists.mysql.com > Subject: Re: Can I measure the use of index? > > > > Am 15.10.2012 17:24, schrieb Carlos Eduardo Caldi: > > > > Afternoon > > > > Somebody knows how can I log or measure the index use ? > > explain select whatever from table where bla=value >
Re: Can I measure the use of index?
Am 15.10.2012 17:24, schrieb Carlos Eduardo Caldi: > > Afternoon > > Somebody knows how can I log or measure the index use ? explain select whatever from table where bla=value signature.asc Description: OpenPGP digital signature
