2

I added the following to my.ini

log_slow_queries  = "C:\Program Files (x86)\MySQL\MySQL Server 5.0\mysql-slow.log" 
long_query_time  = 3 
log-queries-not-using-indexes

I restarted the MySQL service . The slow queries are getting listed in mysql-slow.log but the issue is all the queries gets listed . I want to list only those queries which take more than 3 seconds

The following is an extract from mysql-slow.log:

# Query_time: 0  Lock_time: 0  Rows_sent: 29  Rows_examined: 29
SHOW TABLES LIKE 'tblRaw2%';

Here Query_time is listed as 0 I want only those queries which takes more than 3 seconds . If there is any more fields to be set in my.ini please point out

Thanks

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Anuj
  • 9,222
  • 8
  • 33
  • 30
  • show tables command, mysql internally might treat this as queries not using index, do you have examples other that the above? – ajreal Aug 24 '11 at 09:12
  • yes .. it happens for specific tables also. for eg i have a table by the name tblRaw20110823 and if I do 'select * from tblRaw20110823' it gets listed with Query_time: 0 – Anuj Aug 24 '11 at 09:21

3 Answers3

3

Your database probably doesn't have indexes and you've defined that :

log-queries-not-using-indexes should be logged.

remove log-queries-not-using-indexes and try again.

Pedro Lobito
  • 94,083
  • 31
  • 258
  • 268
3

this might be helpful

http://dev.mysql.com/doc/refman/5.1/en/server-options.html#option_mysqld_log-queries-not-using-indexes

If you are using this option with the slow query log enabled, queries that are expected to retrieve all rows are logged. See Section 5.2.5, “The Slow Query Log”. This option does not necessarily mean that no index is used. For example, a query that uses a full index scan uses an index but would be logged because the index would not limit the number of rows.

your both scenarios are just the cases

ajreal
  • 46,720
  • 11
  • 89
  • 119
0

In case you are looking for information related to how to log MySQL slow queries and analyze them, check my latest post on the subject at http://www.installationpage.com/mysql/mysql-importance-slow-log-processing/

arunchinnachamy
  • 196
  • 2
  • 6