Comparing the Performance of InnoDB, MariaDB, and MySQL

Feedback

Question:

Query

select * from pmt_bolok 
where id in (select max(id) from  pmt_bolok 
            group by bolok_row)  

The MariaDB query for version 10.0.17 had a fast execution time of 0.094 seconds, with an additional 0.047 seconds spent on network-related tasks.

However, it took a total of 00:05:44 seconds for mysql(5.5.32) to complete, including the additional 478 seconds for network processing which caused a significant decrease in speed.

The primary key field in my Innodb table is marked with

id

.

All of the my.ini files are identical, with the exception of the port and MySQL pathway.

[client] 
port            = 3306 
socket          = "C:/xampp/mysql/mysql.sock"
[mysqld]
autocommit=1
port= 3306
socket = "C:/xampp/mysql/mysql.sock"
basedir = "C:/xampp/mysql" 
tmpdir = "C:/xampp/tmp" 
datadir = "C:/xampp/mysql/data"
pid_file = "mysql.pid"
# enable-named-pipe
key_buffer = 16M
max_allowed_packet = 1M
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M
log_error = "mysql_error.log"
plugin_dir = "C:/xampp/mysql/lib/plugin/" 
server-id   = 1
innodb_data_home_dir = "C:/xampp/mysql/data"
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = "C:/xampp/mysql/data"
innodb_buffer_pool_size = 16M
innodb_additional_mem_pool_size = 2M
innodb_log_file_size = 5M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50
[mysqldump]
quick
max_allowed_packet = 16M
[mysql]
no-auto-rehash
[isamchk]
key_buffer = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M
[myisamchk]
key_buffer = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M
[mysqlhotcopy]
interactive-timeout


Question:

What is causing the slow speed of this MySQL query?

What are some ways to enhance the speed of a MySQL query?

Other than my.ini, what distinguishes mysql from mariadb?


Solution 1:

Either migrate to MySQL 5.7 or modify your query.

select * from pmt_bolok order by id desc limit 1

The reason behind this occurrence is that MySQL version 5.5 lacks
subquery result
materialization, causing the execution of subqueries in the WHERE clause for every row of the table.


Solution 2:


The optimization of

IN ( SELECT ... )

was subpar in versions 5.5 and earlier, despite attempts to improve it.

For improved performance in any version, use the query type known as “Groupwise max”. Follow this link for more information.

When it comes to your settings, it’s important to remember that tweaking them won’t necessarily solve performance issues. Nonetheless, if you’re utilizing InnoDB, it’s recommended to set

innodb_buffer_pool_size

at approximately 70% of the total RAM available. Doing so can enhance the performance of all your queries. However, if you’re using MyISAM, it’s best to avoid this practice altogether.

Frequently Asked Questions