What is the reasonable time for a mysql-query?

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 reaso开发者_如何转开发nable time here?

Cheers.

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.

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.

There's no worry as long as there's no problem. A query can take up to one hour if it crunches extremely large amount of data once every 6 months in a system where only it is running. It won't be a problem.

Another query can take 100ms only but it's on a web server and 1000 persons are connecting simultaneously!

That's 100 seconds, and almost 2 minutes for 1000 users simultaneously. If you can reduce it's resource consumption by half by say... adding an index or removing a cursor, you could reduce your server's resource consumption by half and give an better experience of your website to users.

So the trick to know which query is good or bad is to keep track of every query running on a system, and aggregate it's disk IO, CPU usage and running time then optimize the problematic ones.

That's a hard question, it depends a lot on your system, DB schema and mysql config. I can advice you to use mysqltuner (theyselves) to be sure that your config and your Db give you the best of themselves.

In the other hand, the queries should be fast as hell to give a good user experience.

Regards