Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 400
  • Last Modified:

The optimization process for bad queries

but  when I disable the log_query_not-using-indexes I dont have results that are less than a second, but than again i dont have any result at all.
so how can I enable log_query_not-using-indexes and read and change the relevant result properly?
0
Nura111
Asked:
Nura111
  • 4
  • 3
  • 2
3 Solutions
 
K VDatabase ConsultantCommented:
In my.cnf add following and restart mysql server:

log_queries_not_using_indexes=on
0
 
Nura111Author Commented:
Thanks but its not what I meant I know how to enable that. but than at the result I get a lot of queries some of them seem like a system queries and im not sure which one to take into consideration and try to change
0
 
Muhammad WasifCommented:
Which version of MySQL you are using? According to MySQL manual
"As of MySQL 5.1.21, the minimum value is 0, and a resolution of microseconds is supported when logging to a file. However, the microseconds part is ignored and only integer values are written when logging to tables. Prior to MySQL 5.1.21, the minimum value is 1, and the value for this variable must be an integer."

What you have defined for long_query_time in my.cnf?

This question and previous one are purely MySQL questions. You should edit the zones of this question.
0
Evaluating UTMs? Here's what you need to know!

Evaluating a UTM appliance and vendor can prove to be an overwhelming exercise.  How can you make sure that you're getting the security that your organization needs without breaking the bank? Check out our UTM Buyer's Guide for more information on what you should be looking for!

 
Nura111Author Commented:
MySql 5.1.21 - and the long_query_time =1. but it doesn't seem like I have results for that. i dont think i use the microseconds part beacuse I set the long_query_time to 1 and no 0. but i was talking about log_queries_not_using_indexes one that im haveing a hard time to understand what the result mean and how to change them
0
 
Muhammad WasifCommented:
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.

You need to examine the queries to see the columns used in JOIN, WHERE clauses  are properly indexed are not. Use EXPLAIN to optimize the queries.

Can you provide a couple of queries and the schema of tables involved in those queries?
0
 
Nura111Author Commented:
ok so technically I need to take into consideration every result in the slow log file?
#even if the user@host is the system? (and the table is 'information_schema')
#the row sent is 0?

what does the lock time mean? do I need to take it into consideration while looking at the result?

0
 
K VDatabase ConsultantCommented:
you dont have to consider information_schema data atall.
http://bugs.mysql.com/bug.php?id=44917
0
 
Nura111Author Commented:
is it possible that the log file is changing in a way that he delete those queries results from the file?
as today they are not their anymore
0
 
Muhammad WasifCommented:
Locked state "The query is locked by another query."
http://dev.mysql.com/doc/refman/5.1/en/general-thread-states.html

You should not consider Lock time, because query is waiting to executing in this state.
0

Featured Post

Who's Defending Your Organization from Threats?

Protecting against advanced threats requires an IT dream team – a well-oiled machine of people and solutions working together to defend your organization. Download our resource kit today to learn more about the tools you need to build you IT Dream Team!

  • 4
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now