Announcement

Announcement Module
Collapse
No announcement yet.

Queries are well optimized but they are causing many slow queries

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Queries are well optimized but they are causing many slow queries

    Hello,
    I have many queries that are well optimized using indexes.

    when I execute these queries in phpmyadmin using profiling, they always show something like 0.00032 sec etc..

    Example:
    SELECT id FROM table WHERE column='something' limit 1

    But when I look in the slow query log, i find a lot of these queries that exceed 2 seconds.

    In this table, I estimate that there are around 99.9% selects and 0.1% updates.
    I have used low_priority in those update queries.

    Inserts and deletes are carried on once in a few days just for a few seconds.
    So, I think they may not be causing these slow queries.

    Is there any locking issue that is causing slow queries?
    If yes, how to solve it.

    Can you please inform me why the queries are performing very well in phpmyadmin but are causing slow queries?

    Let me know if you would like to know more information.

    Thank you

  • #2
    Hi,

    One thing to keep in mind is that it is possible, maybe even probable that the reason the query is showing up is because there is no index on the table that it can use. In this case, on 'column'

    This is an option in Mysql 5.0...

    "In MySQL 5.0, queries that do not use indexes are logged in the slow query log if the --log-queries-not-using-indexes option is specified."

    You can check it out here:
    http://dev.mysql.com/doc/refman/5.0/...query-log.html

    Hope that helps.
    -T

    Comment


    • #3
      teajay2 wrote on Thu, 17 July 2008 22:30

      Hi,

      One thing to keep in mind is that it is possible, maybe even probable that the reason the query is showing up is because there is no index on the table that it can use. In this case, on 'column'

      This is an option in Mysql 5.0...

      "In MySQL 5.0, queries that do not use indexes are logged in the slow query log if the --log-queries-not-using-indexes option is specified."

      You can check it out here:
      http://dev.mysql.com/doc/refman/5.0/en/slow-query-log.html

      Hope that helps.
      -T


      Hello Teajay,
      I use indexes and 'column' has index.
      When run EXPLAIN, it displayes that it is using index.
      So, i think there is no problem with the index

      Is there any other reason for slow queries other than the use of indexes?

      Thank you

      Comment

      Working...
      X