Check function in Mysql (Incredible results)
I have 2 tables:
author with 3 million lines.
book with 20 mile rows.
...
So, I compared this query to a connection:
SELECT BENCHMARK(100000000, 'SELECT book.title, author.name
FROM `book` , `author` WHERE book.id = author.book_id ')
And this is the result:
The request took 0.7438 seconds
ONLY 0.7438 seconds for 100 million connection requests
Am I making some mistakes or is this the correct result?
a source to share
Your result smells bad, I just checked the documentation and did some tests of my own. You are not really comparing anything.
BENCHMARK () is for testing scalar expressions, it is not for checking query execution times. The request is not actually being executed. In my own testing of requests, the duration was not related to the complexity of the request, but only to the number of probes to be performed.
Take a look at http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_benchmark
Several quotes from the doc:
"BENCHMARK () is designed to measure the performance of scalar expressions at runtime,"
"Only scalar expressions can be used. Although an expression can be a subquery, it must return one column and no more than one row. For example, BENCHMARK (10, (SELECT * FROM t)) will fail if table t has more than one column or more one line. "
You are not really measuring anything, except that most of the time the query scheduling time is.
If you want to run tests, it's probably worth doing it from your application code (and possibly with a caching directive depending on how strong your prod is going to be). Doing this from app code will also show the time to hydrate the data, as well as the cost of sending the data over the wire, etc.
a source to share