CODEWITHSUNDEEP
mysqlmariadbmax
Joel
Joel

Reputation: 199

MySQL 5.7 to Maria DB 10.2 Max function 10X Slower

MySQL 5.7 to MariaDB 10.2 Max function 10X Slower

I have two identical servers same hardware one is running MySQL 5.7 and the other upgraded to Maria DB 10.2 The query on the MySQL 5.7 server takes 84 ms and the same query on MariaDB 10.2 takes 9.834 seconds. I don't understand why the explain output on the MySQL 5.7 server does not show any possible keys. I also don't understand why the MAX function is 10 times slower on MariaDB. If I remove the MAX function on the Maria DB server that query runs in 81ms. I also can change the query to use DISTINCT and then wrap that in a select and do the MAX on the outside query.

Table Statistics:

Tables are identical on each server.

Among other indexes, there are the following indexes

MYSQL 5.7 Server

Query: SELECT MAX(year) AS Year FROM t WHERE e0212 = '999999';

Execution time: 84 ms

EXPLAIN OUTPUT:

id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE Select tables optimized away

MariaDB 10.2 Server

Query: SELECT MAX(year) AS Year FROM t WHERE e0212 = '999999';

Execution time 9.834 s

EXPLAIN OUTPUT:

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t ref e0212 e0212 20 const 5498670 Using index condition

Refactored query: SELECT MAX(a.year) FROM ( SELECT DISTINCT(year) AS Year FROM t WHERE e0212 = '999999' ) as a;

Execution time 81ms

Upvotes: 0

Views: 299

Answers (1)

Rick James
Rick James

Reputation: 142503

Caching

10x smells like simply a caching issue. The faster machine had all the blocks it needed already cached in RAM; the slower one needed to do the query.

Or, the data needed was smaller than the setting of innodb_buffer_pool_size. Again, this is a caching issue.

Run the query twice on each machine.

Better index

Both machines will run faster with INDEX(e0212, year) in that order. And, if you add that index, Drop your current INDEX(e0212) since it gets in the way of picking the optimal index.

The suggested refactoring is unlikely to help.

Further debugging

For further discussion, please provide EXPLAIN FORMAT=JSON SELECT ... from each machine.

Every table should have a PRIMARY KEY. The lack of one probably does not impact this query.

Upvotes: 0

Related Questions