Not the answer you need?
Register and ask your own question!
Many Forum changes were implemented on Tue 22 Sep. Read about new Ranks, Scoring, and Reactions.
Email [email protected] for any comments or concerns.

Queries are well optimized but they are causing many slow queries

studentstudent EntrantCurrent User Role Beginner
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

Comments

  • teajay2teajay2 Entrant Current User Role Beginner
    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
  • studentstudent Entrant Current User Role Beginner
    <table border="0" align="center" width="90%" cellpadding="3" cellspacing="1"><tr><td class="SmallText">teajay2 wrote on Thu, 17 July 2008 22:30</td></tr><tr><td class="quote">
    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
    </td></tr></table>

    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
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.