I have 2 tables:
author with 3 millions of rows.
book with 20 miles rows.
.
So I have benchmarked this query with a join:
SELECT BENCHMARK(100000000, 'SELECT book.title, author.name
FROM `book` , `author` WHERE book.id = author.book_id ')
And this is the result:
Query took 0.7438 sec
ONLY 0.7438 seconds for 100 millions of query with a join ???
Do I make some mistakes or this is the right result ?
Your result smells wrong, I've just run checked the documentation and run some benchmarks of my own. You're not actually benchmarking anything.
BENCHMARK() is for testing scalar expressions, it's not for testing query runtimes. The query isn't actually being executed. In my own testing of queries, the duration took was not at all related to the complexity of the query, only to the amount of trials to be run.
Take a look at http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_benchmark
A few quotes from the doc:
"BENCHMARK() is intended for measuring the runtime performance of scalar expressions,"
"Only scalar expressions can be used. Although the expression can be a subquery, it must return a single column and at most a single row. For example, BENCHMARK(10, (SELECT * FROM t)) will fail if the table t has more than one column or more than one row."
You're not actually measuring anything, outside of at absolute most the query planners time.
If you want to run benchmarks, it's probably worth doing it from application code (and possible with a no cache directive depending on how write heavy your prod environment will be.). Doing it from application code will also figure in the time to hydrate the data, plus the cost of sending the data across the wire etc.