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 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
Can I measure the use of index?
Afternoon Somebody knows how can I log or measure the index use ? Thanks Carlos
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
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?
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
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 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
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
