MySQL Single query performance comparison

According to the morgo suggestion suggested by morgo, I did some small tests on the queries and datasets mentioned in Impact of column types on MySQL JOIN performance, but I found another problem: response time (aka MySQL versions ).

The answer

To put it simply. As a good consultant, these conclusions are prerequisite :-)

The test

 
 

  
  
SELECT * 
  
  
  FROM a 
  
  
  JOIN b ON b.a_id = a.id 
  
  
 WHERE a.id BETWEEN 10000 AND 15000 
  
  
; 
 
 

All of the following tests are based on the same query statement.

MySQL-related parameter settings are as follows. Do I still need to consider the join buffer or other buffers (read_buffer_size, read_rnd_buffer_size, join_buffer_size) of a single session?

 
 

  
  
innodb_buffer_pool_size        = 768M 
  
  
innodb_buffer_pool_instances   = 1 
  
  
innodb_file_per_table          = 1 
 
 

The results

The Graph

Conclusion

Do not trust the benchmark indicators. They are mostly meaningless for your specific workload and purely marketing activities ..., Including the above mentioned !; -)

Database vendors Oracle, MySQL, Percona, and MariaDB) focus on throughput and features. Basically, the benchmark indicators represent the performance cost of a single query.

Facebook, LinkedIn, Google, Wikpedia, Booking.com, Yahoo! I think so when MySQL users are more interested in throughput than the performance of a single query ). But most MySQL users 95%) won't have throughput problems, but there will be query performance problems here I assume that the same is true for Oracle, DB2, MS-SQL Server, PostgreSQL, etc ).

Therefore, the database supplier's products are not for the public, but for some specific users/customers who may pay for them ).

Let me go back to the discussion about data:

The first assumption: "the past time is always better" is absolutely incorrect. MySQL 4.0 and 4.1 are only special cases. The rough estimation trend based on MySQL5.0 is: new versions over time) single query performance will become worse. I think this applies to other databases too...

Like some claims: "We have the fastest MySQL" or "We have hired the entire Optimization Team" does not need to be reflected in the performance of a single query. At least not for a specific query.

Therefore, in summary, If you upgrade MySQL <-> Percona <-> MariaDB), you need to carefully test it! Traps are unpredictable. A newer MySQL version may increase the performance of your application. Children, do not trust the market too much.

Illusion

We have found some false ideas during this small test:

The optimization introduced in MySQL 5.0 is not being optimized !?!), Greatly accelerate a single specific query.

MariaDB's 5.2 and 5.3 have poor performance in this specific query.

I don't know why the Galera cluster has shown that 5.5 is the best version. This is not intentional or manipulated! This result is really bad luck. But I like it! :-)

MySQL 5.6 seems to have some problems with these queries. Maybe Oracle has brought too many improvements to MySQL. Why? (Too many □') Too many other

In Percona version 5.6, these queries are sometimes better than normal MySQL, but sometimes Oracle optimization causes a sharp slowdown in Percona speed. Therefore, a particularly bad result is displayed. I don't know why. My first reaction was external influence. But I have the ability to reproduce this mess once ). So I think there must be something inside Percona, such as AHI ?).

Last

The two countries cannot fight!

If you are not satisfied with the computing results released here, you want to recalculate them. Or, please let me know what is missing here.

If you do not agree with this conclusion, please let me know. I know the new

This test is very interesting today. My MyEnv also provides a lot of help for this test.

If you need us to perform this test for you, please contact us. Our consulting team consulting will be happy to provide you with answers to various questions.

Http://www.oschina.net/translate/mysql-single-query-performance-the-truth.