CODEWITHSUNDEEP
mysqlsqlperformancemariadb
Vladimir
Vladimir

Reputation: 157

Simple query: mysql - very slow , mariadb - good performance

Simple query:

select *
from data.staff AS staff
left join data.contact AS workphones on staff.id = workphones.staff_with_work_phone_id

Mysql run time: 5.3 sec.
MariaDb run time: 0.016 sec.

Contact has ~50000 rows.
Staff has ~600 rows.

What is the reason?
Is it possible to achieve the same result on mysql?

Thank you!

Explain MySql (v5.7.14):

+----+-------------+------------+------------+------+--------------------------------+------+---------+------+-------+----------+---------------------------------------+
| id | select_type | table      | partitions | type | possible_keys                  | key  | key_len | ref  | rows  | filtered | Extra                                 |
+----+-------------+------------+------------+------+--------------------------------+------+---------+------+-------+----------+---------------------------------------+
| 1  | SIMPLE      | staff      | NULL       | ALL  | NULL                           | NULL | NULL    | NULL | 606   | 100.00   | NULL                                  |
+----+-------------+------------+------------+------+--------------------------------+------+---------+------+-------+----------+---------------------------------------+
| 2  | SIMPLE      | workphones | NULL       | ALL  | FK_2f7824065c2c4b0fbe5c00da271 | NULL | NULL    | NULL | 49180 | 100.00   | Using where.                          |
|    |             |            |            |      |                                |      |         |      |       |          | Using join buffer (Block Nested Loop) |
+----+-------------+------------+------------+------+--------------------------------+------+---------+------+-------+----------+---------------------------------------+

Explain MariaDB (v10.0.28):

+----+-------------+------------+------+--------------------------------+--------------------------------+---------+--------------------+-------+----------+-------+
| id | select_type | table      | type | possible_keys                  | key                            | key_len | ref                | rows  | filtered | Extra |
+----+-------------+------------+------+--------------------------------+--------------------------------+---------+--------------------+-------+----------+-------+
| 1  | SIMPLE      | staff      | ALL  |                                |                                |         |                    | 602   | 100.00   |       |
+----+-------------+------------+------+--------------------------------+--------------------------------+---------+--------------------+-------+----------+-------+
| 2  | SIMPLE      | workphones | ALL  | FK_1249f6bc1d68495090691f3ce02 | FK_1249f6bc1d68495090691f3ce02 | 9       | user_data.staff.id | 25476 | 100.00   |       |
+----+-------------+------------+------+--------------------------------+--------------------------------+---------+--------------------+-------+----------+-------+

The rest of the verification conditions are identical. The test was conducted many times.

Upvotes: 0

Views: 746

Answers (1)

flutter
flutter

Reputation: 754

Your two query plans show you why MySQL is slower. Both find the possible keys, which is a foreign key.

MariaDB will USE the FK: FK_1249f6bc1d68495090691f3ce02 is in both columns possible_keys AND keys in row 2.

MySQL does see the FK, but does NOT use it. MySQL tells you, that it will use a

Using join buffer (Block Nested Loop)

in the EXTRA table.

MySQL does not use your Foreign Key.

Foreign Key Joins Do you have an index on your foreign key in both database systems? If only MariaDB has it, then you cannot blame MySQL, because it cannot use, what it does not have.

Upvotes: 1

Related Questions