The difference between the MySQL execution plan and MariaDB

Question

Is there a difference between the MySQL execution plan and MariaDB Query?

If so, which one is better?

CREATE TABLE `Table1` ( `ID` int(11) NOT NULL, KEY `ID` (`ID`) ); CREATE TABLE `Table2` ( `ID` int(11) NOT NULL, KEY `ID` (`ID`) ); CREATE TABLE `Table3` ( `ID` int(11) NOT NULL, PRIMARY KEY (`ID`) ); 

In Mary DB,

 MariaDB [truepay_psr]> explain select T1.ID FROM Table1 T1 LEFT JOIN (SELECT T1.ID FROM Table3 T1 LEFT JOIN Table2 T2 ON T1.ID = T2.ID WHERE T2.ID IS NULL) T2 ON T1.ID=T2.ID WHERE T2.ID IS NULL; +------+-------------+-------+--------+---------------+---------+---------+-------------------+------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+-------+--------+---------------+---------+---------+-------------------+------+--------------------------+ | 1 | SIMPLE | T1 | index | NULL | ID | 4 | NULL | 1 | Using index | | 1 | SIMPLE | T1 | eq_ref | PRIMARY | PRIMARY | 4 | truepay_psr.T1.ID | 1 | Using where; Using index | | 1 | SIMPLE | T2 | ref | ID | ID | 4 | truepay_psr.T1.ID | 1 | Using where; Using index | +------+-------------+-------+--------+---------------+---------+---------+-------------------+------+--------------------------+ 3 rows in set (0.01 sec) 

In MySQL

 mysql> explain select T1.ID FROM Table1 T1 LEFT JOIN (SELECT T1.ID FROM Table3 T1 LEFT JOIN Table2 T2 ON T1.ID = T2.ID WHERE T2.ID IS NULL) T2 ON T1.ID=T2.ID WHERE T2.ID IS NULL; +----+-------------+------------+--------+---------------+---------+---------+------------+------+--------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+------------+--------+---------------+---------+---------+------------+------+--------------------------------------+ | 1 | PRIMARY | <derived2> | system | NULL | NULL | NULL | NULL | 0 | const row not found | | 1 | PRIMARY | T1 | index | NULL | ID | 4 | NULL | 1 | Using index | | 2 | DERIVED | T1 | index | NULL | PRIMARY | 4 | NULL | 1 | Using index | | 2 | DERIVED | T2 | ref | ID | ID | 4 | test.T1.ID | 1 | Using where; Using index; Not exists | +----+-------------+------------+--------+---------------+---------+---------+------------+------+--------------------------------------+ 4 rows in set (0.00 sec) 

Answer 1

You see the effect of optimizing table optimization (in terms of MySQL, there are 4 rows, and MariaDB has only 3). MariaDB's plan should be the best, as there is "less work." Both should return the same results.

This feature is explained in detail here:

http://s.petrunia.net/blog/?p=58

and here:

https://mariadb.com/kb/en/mariadb/documentation/optimization-and-tuning/query-optimizations/table-elimination/

If you want MariaDB to use the same plan as MySQL, you can ensure its implementation by disabling table elimination optimization with:

 SET optimizer_switch='table_elimination=off';