Page 3 of 3

Re: MySQL Query Performance - Things to know

Posted: Fri Jul 18, 2008 1:40 pm
by Benjamin
Shouldn't the results be based off of an average, rather than best score?

Re: MySQL Query Performance - Things to know

Posted: Sat Jul 19, 2008 2:03 pm
by Dynamis
astions wrote:Shouldn't the results be based off of an average, rather than best score?
I did this for the original results and doing so pulls too many of the computers unknowns into the equation, making the results very unreliable. For example, for the first 2000ish iterations, the query speed actually becomes faster, because of the computer cache, or other variables. After that, anything else running on the computer greatly affects the time as well.

By taking the very fastest query time only, it is taking the time that is affected the least by the computer unknowns. I stick behind my decision thus far saying this is the best way still to measure the data. I may post a list w/ all the results so you can see my decisions on this sometime next week. If you think about it though, taking the very fastest query does indeed make sense. If I were to set up my computer ideally with nothing else running, cache turned off, etc., the time for this fastest query would be the closest time to an ideal system.

Re: MySQL Query Performance - Things to know

Posted: Sat Jul 19, 2008 2:51 pm
by Benjamin
Are you saying that when you used average query times that the differences between the query types became negligible?

I can't see how using the best time is accurate. If you run a query 30k times and the best time is 0.003 seconds, but the mode average is 0.03 it's not accurate to use 0.003 when you compare it against another query because chances are that it will take 0.03 seconds to run, not 0.003.

Re: MySQL Query Performance - Things to know

Posted: Thu Jul 24, 2008 9:33 am
by Dynamis
astions wrote:Are you saying that when you used average query times that the differences between the query types became negligible?

I can't see how using the best time is accurate. If you run a query 30k times and the best time is 0.003 seconds, but the mode average is 0.03 it's not accurate to use 0.003 when you compare it against another query because chances are that it will take 0.03 seconds to run, not 0.003.
Here is the issue with taking the average. First, that time will be an average based off of everything else running on my computer. Since I have other things running, I will occasionally have spikes that will increase the average, time and time again. Second, my computer setup plays a large role in the average time. As I stated, for the first few thousand queries the query time actually speeds up because my computer hardware (memory, cache, etc) is affecting the queries.

How taking the fastest time is the best:
It reduces the effects of my computer's other processes on the query time. No spikes are accounted for. The time is closest to the ideal situation (which would be nothing else running on my computer). This time is actually probably much slower than a properly configured server.

It comes down to this, I had two options and I choose the one I believed would produce the better results. Here is what I based this off of...

1. Take the average time:
Advantages
  • Time you would likely obtain from a set of queries on a development machine with tons of other processes running
Disadvantages
  • Much much slower than a server
  • Less accurate (the speed for the first set of query data[small set] was actually slower than second set[large set] due to computer setup)
  • Unreliable results (due to spikes, the data was everywhere)
2. Take the fastest time:
Advantages
  • Time you would likely obtain from a set of queries on a server with few processes running
  • Reliable data (the results actually make sense and don't vary nearly as much)
Disadvantages
  • Not likely to follow this pattern on a development machine or machine running tons of other processes

Re: MySQL Query Performance - Things to know

Posted: Thu Jul 24, 2008 10:29 am
by Dynamis
Data updated. Check first post for updated information.