100 is tantamount to turning off the log. I prefer 2. select count(ENTITY_NAME) from ALERT_EVENTS where EVENT_TIME > date_sub(now(),INTERVAL 60 MINUTE) and status=upper('failed') and ENTITY_NAME='FETL-ImpressionRC-conversion'; begs for the _compound_ index INDEX(ENTITY_NAME, EVENT_TIME) This would be even better: INDEX(ENTITY_NAME, status, EVENT_TIME)
COUNT(*) should be used if you don't need to check the column for being NULL. > Rows_examined: 141145 That is probably the entire table. Will the table grow? If so, the query will get slower. Meanwhile, the index I suggested will (probably) be much faster. > -----Original Message----- > From: Suresh Kuna [mailto:sureshkumar...@gmail.com] > Sent: Saturday, September 01, 2012 1:03 AM > To: Adarsh Sharma > Cc: mysql@lists.mysql.com > Subject: Re: Understanding Slow Query Log > > Disable log-queries-not-using-indexes to log only queries > 100 sec. > > Just do "> /var/lib/mysql/slow-queries.log" it will clear the log. > > On Sat, Sep 1, 2012 at 12:34 PM, Adarsh Sharma > <eddy.ada...@gmail.com>wrote: > > > Hi all, > > > > I am using Mysql Ver 14.14 Distrib 5.1.58 in which i enabled slow > > query log by setting below parameters in my.cnf : > > > > log-slow-queries=/usr/local/mysql/slow-query.log > > long_query_time=100 > > log-queries-not-using-indexes > > > > I am assuming from the inf. from the internet that long_query_time is > > in seconds , but i see the slow query log , there are lots of > > statements ( queries ) : > > > > # User@Host: user1[user1] @ abc.dd.aa.com [192.112.111.111] # > > Query_time: 0.052784 Lock_time: 0.000043 Rows_sent: 1 > Rows_examined: > > 141145 > > SET timestamp=1346409734; > > select count(ENTITY_NAME) from ALERT_EVENTS where EVENT_TIME > > > date_sub(now(),INTERVAL 60 MINUTE) and status=upper('failed') and > > ENTITY_NAME='FETL-ImpressionRC-conversion'; > > # Time: 120831 10:43:14 > > # User@Host: user1[user1] @ abc.dd.aa.com [192.112.111.111] # > > Query_time: 0.053599 Lock_time: 0.000079 Rows_sent: 1 > Rows_examined: > > 141145 > > SET timestamp=1346409794; > > select count(ENTITY_NAME) from ALERT_EVENTS where EVENT_TIME > > > date_sub(now(),INTERVAL 60 MINUTE) and status=upper('failed') and > > ENTITY_NAME='FETL-click-enhancer-deferred'; > > # User@Host: user1[user2] @ abc.dd.aa.com [192.112.111.111] # > > Query_time: 0.054185 Lock_time: 0.000086 Rows_sent: 1 > Rows_examined: > > 141145 > > SET timestamp=1346409794; > > select count(ENTITY_NAME) from ALERT_EVENTS where EVENT_TIME > > > date_sub(now(),INTERVAL 60 MINUTE) and status=upper('failed') and > > ENTITY_NAME='FETL-ImpressionRC-conversion'; > > # Time: 120831 10:43:22 > > # User@Host: user2[user2] @ abc.dd.aa.com [192.112.111.111] # > > Query_time: 0.000163 Lock_time: 0.000045 Rows_sent: 1 > Rows_examined: > > 13 > > > > > > > > I don't understand the query time unit in slow query log because i > > expect queries to be logged that takes > 100 s. I tested with sleep > > command for 60s , it doesn't logged in slow query log and when i > sleep > > for 120 s it logged but i don't why the other queries are logging in > slow log. > > > > # Query_time: 120.000259 Lock_time: 0.000000 Rows_sent: 1 > Rows_examined: > > 0 > > SET timestamp=1346443103; > > SELECT SLEEP(120); > > > > And also my slow log is increasing and decided to purge thorogh below > > command : > > > > cat /dev/null > /var/lib/mysql/slow-queries.log > > > > > > Anyone any ideas about this. > > > > > > Thanks > > > > > > -- > Thanks > Suresh Kuna > MySQL DBA -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql