MySQL Query Performance - Things to know
Moderator: General Moderators
Re: MySQL Query Performance - Things to know
Shouldn't the results be based off of an average, rather than best score?
Re: MySQL Query Performance - Things to know
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.astions wrote:Shouldn't the results be based off of an average, rather than best score?
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
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.
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
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.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.
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
- 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)
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)
- Not likely to follow this pattern on a development machine or machine running tons of other processes
Re: MySQL Query Performance - Things to know
Data updated. Check first post for updated information.