Menu
Well, over the time I have seen queries that takes time like 0.0003 seconds (in phpmyadmin), and then there are also queries that takes 60 seconds or more.
I was wondering what the mysql gurus think here, beyond what time I should start worrying that my mysql query is not optimized enough, what's the reasonable time here?
Cheers.
869
The query takes 20 to 500 ms (or sometimes more) depending on the system and the amount of data. The performance of the database or the database server has a significant influence on the speed.
The slow query log consists of SQL statements that take more than long_query_time seconds to execute and require at least min_examined_row_limit rows to be examined. The slow query log can be used to find queries that take a long time to execute and are therefore candidates for optimization.
There are a number of things that may cause a query to take longer time to execute: Inefficient query – Use non-indexed columns while lookup or joining, thus MySQL takes longer time to match the condition. Table lock – The table is locked, by global lock or explicit table lock when the query is trying to access it.
The maximum length of a VARCHAR in MySQL is subject to the maximum row size of 65,535 bytes, which is shared among all columns except TEXT/BLOB columns and the character set used. It means the total column should not be more than 65535 bytes.
It really depends on the nature of the query, if its something that is done in the back end without no user interaction then it can take tens of minutes in a large DB. e.g. data manipulation, data migration etc...
But if it is to retrieve data for real time user interaction such as showing a website or getting details to show to the user than its how long your user is willing to wait for. Personally i try to keep my total query for all actions at a given time under 1-3 seconds(for serious data crunching stuff) usually start to optimise at 1 second mark.
128
You should start worrying when the query takes so long that usability suffers. For queries that are initiated by a website request, this time is short (the user wants to see the page quickly); for a non-critical batch update, this time is long (should be finished before the next batch update arrives).
On the other hand, you should know what the MySQL Reference Manual says about Optimization to recognize potential for improvement.
24
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
