MySQL is extremely slow on EC2

问题:

I have found out that MySQL on EC2 (Ubuntu 12.10) could be extremely slow. It takes just 700ms for a certain set of SQL queries to perform on my local PC (Windows 7), whereas on EC2 it requires more than 13sec.

The database is very small, just 12MB. There is almost no disk IO during the query. Nevertheless, EC2 instance is 20 times slower. All the databases are based on the same dump: same tables and same indexes. The queries return the same results. The only difference is the execution time.

I tried M1.small and M2.xlarge (which has 7 times more computing power than M1.small) - the outcome is the same: queries take almost the same time on both servers and both are extremely slow.

Why could this happen?

回答1:

The problem was with MySQL 5.5 which executes subqueries inefficiently. My home PC run MySQL 5.6 which is far better in this regard. So I upgraded MySQL on EC2 to version 5.6 and it became almost as fast as my home PC (as far as only one simultaneous query is concerned).