Query
select * from pmt_bolok
where id in (select max(id) from pmt_bolok
group by bolok_row)
This query in MariaDB(10.0.17) execute very nice in 0.094 sec. (+ 0.047 sec. network)
But in mysql(5.5.32) is very slow in 00:05:44 sec. (+ 478 sec. network)
My table is innodb and id field is primary key.
all my.ini is equal except port and mysql path
[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:
Why speed this query is very very slow in mysql?
How can increment speed this query in mysql?
Except my.ini what is different between mysql and mariadb?
Consider moving to MySQL 5.7 or rewrite your query to
select * from pmt_bolok order by id desc limit 1
As for why it happens - MySQL 5.5 doesn't have subquery result materialization and subquery in your WHERE clause will be executed for each row in the table.