Re: Can I measure the use of index?

2012-10-15 Thread Eric Bergen
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, >

Re: Can I measure the use of index?

2012-10-15 Thread Lixun Peng
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_sc

Re: mysql logs query with indexes used to the slow-log and not logging if there is index in reverse order

2012-10-15 Thread spameden
Thanks a lot for all your comments! I did disable Query cache before testing with set query_cache_type=OFF for the current session. I will report this to the MySQL bugs site later. 2012/10/16 Rick James > **Ø **My initial question was why MySQL logs it in the slow log if the > query uses

RE: mysql logs query with indexes used to the slow-log and not logging if there is index in reverse order

2012-10-15 Thread Rick James
Ø My initial question was why MySQL logs it in the slow log if the query uses an INDEX? That _may_ be worth a bug report. A _possible_ answer... EXPLAIN presents what the optimizer is in the mood for at that moment. It does not necessarily reflect what it was in the mood for when it ran the

RE: mysql logs query with indexes used to the slow-log and not logging if there is index in reverse order

2012-10-15 Thread Rick James
I don't fully understand Handler numbers, either. But note the vast difference in Handler_read_next, as if the second test had to read (sequentially scan) a lot more stuff (in the index or the data). Summary: INDEX(time, priority) -- slower; bigger Handler numbers; shorter key_len; filesort

Re: mysql logs query with indexes used to the slow-log and not logging if there is index in reverse order

2012-10-15 Thread spameden
Sorry, forgot to say: mysql> show variables like 'long_query_time%'; +-+---+ | Variable_name | Value | +-+---+ | long_query_time | 10.00 | +-+---+ 1 row in set (0.00 sec) It's getting in the log only due: mysql> sh

Re: mysql logs query with indexes used to the slow-log and not logging if there is index in reverse order

2012-10-15 Thread spameden
Sorry, my previous e-mail was a test on MySQL-5.5.28 on an empty table. Here is the MySQL-5.1 Percona testing table: mysql> select count(*) from send_sms_test; +--+ | count(*) | +--+ | 143879 | +--+ 1 row in set (0.03 sec) Without LIMIT: mysql> desc select * from send_s

RE: mysql logs query with indexes used to the slow-log and not logging if there is index in reverse order

2012-10-15 Thread Rick James
* Rows = 11 / 22 -- don't take the numbers too seriously; they are crude approximations based on estimated cardinality. * The 11 comes from the LIMIT -- therefore useless in judging the efficiency. (The 22 may be 2*11; I don't know.) * Run the EXPLAINs without LIMIT -- that will avoid the bogu

Re: mysql logs query with indexes used to the slow-log and not logging if there is index in reverse order

2012-10-15 Thread spameden
Hi, I've just checked on MySQL-5.5.28 it acts absolutely same. I need to use (priority,time) KEY instead of (time, priority) because query results in better performance. With first key used there is no need to sort at all, whilst if using latter: mysql> *desc select * from send_sms_test FORCE IN

mysql logs query with indexes used to the slow-log and not logging if there is index in reverse order

2012-10-15 Thread spameden
Hi, list. Sorry for the long subject, but I'm really interested in solving this and need a help: I've got a table: mysql> show create table send_sms_test; +---+

Re: innodb_lock_wait_timeout

2012-10-15 Thread Johan De Meersman
- Original Message - > From: "trimurthy" > > hi sir even i also have a doubt regarding the connections. suppose if > there is an existing connection to the server with the user name > "xxx" and password "" if i send another request with the same user name > and password then what will

Re: Can I measure the use of index?

2012-10-15 Thread Perrin Harkins
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:h

RE: Can I measure the use of index?

2012-10-15 Thread Carlos Eduardo Caldi
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 Edu

Re: Can I measure the use of index?

2012-10-15 Thread Reindl Harald
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

Can I measure the use of index?

2012-10-15 Thread Carlos Eduardo Caldi
Afternoon Somebody knows how can I log or measure the index use ? Thanks Carlos

Re: innodb_lock_wait_timeout

2012-10-15 Thread Johan De Meersman
- Original Message - > From: "Markus Falb" > > With a low timeout the connection will be terminated sooner, but if > the application retries another connection is taken. I could have raised > the timeout with the same effect on the db side (1 process is waiting) > but maybe more performan