Target mysql query response times

Question

As I'm looking to optimize some of my mysql queries and improve performance, I'm realizing that I'm just not sure what sort of response times I should be aiming for. Obviously lower is better, but what do you normally target as a 'slow' query for a consumer site?

My queries are currently running between 0.00 to 0.70 seconds.

I assume 0.00 is pretty good, but what about 0.70 seconds? I assume I should be looking for improved performance on that? If so, what do you usually aim for?

-----A bit more info about my query to answer your questions (though I really am interested in yours & what you do ----

My queries are on joined tables with between 1-3 million rows (sometimes more). I run my queries on the same box which has 4gb of RAM and an SATA drive. That box runs 6 different databases two of which are regularly being queried, one regularly being updated. The times I provided are for the 'read' database which is rarely written to.

For web requests, I am running between 1 & 3 queries (but I cache the data using memcache as well for performance).

The times I gave were from the mysql response from command line terminal to the machine (so I assume that is actually runtime).

The queries have many joins, between 4 & 7 depending on the query.

Answer 1

it depends - if you're in the same machine as the DB or in another machine. it also depende which kind of query, how many joins, how many rows, do you use index to pull the data, So there is no good answer: "A Query should take this and that long".

I'm running some complicated queries, between two machines, and it takes between 780 and 850ms- and that means nothing to you.... (probably on the same machine they will be shorter..., over an internet connection - longer ect...)

Answer 2

It really depends on how many queries you are executing on a page and what acceptable response times are for your application.

A 100 second query might be okay for a data mining application, whereas a 2 second query is probably unacceptable for a web application.

When working on web applications, I have a debugging feature that I can turn out that outputs queries and execution times at the bottom of the page. Anything .5-1 second gets flagged with yellow and anything higher gets flagged with red. I have a link that I can click to get explain output for quick optimization.

Of course, if you have a web page with dozens of queries running at .3 seconds each, you have a problem.

If you are good at reading the explain for a query, then you can get an idea of how well you have optimized your queries. Red flags are things like temporary tables and lack of indexes. As far as absolute times, I think you will have a hard time finding a hard and fast rule on what 'fast' is.

Answer 3

It depends. Normally on smallish queries (under 50 000 rows queried), I'd expect < 150-200ms, but it depends on the hardware and environment.

In your example, you mention 700ms. What is the query doing? How much RAM is in the box? What disks are you using? These all affect the outcome.