1

After switching DB engine from MyIsam to InnoDB on MariaDB 10.3.39 we noticed endless queries and huge disk space usage. The problem are some queries that are wrong optimized with the new engine.

Example:

explain SELECT table1.id, table1.column1, table1.column2, table1.column3, table1.column4, table1.column5, table4.column1, table3.column1, table3.column2, table3.id, table2.id, table2.column1 
FROM table1 INNER JOIN table2 ON table2.id = table1.table2_foreign_key INNER JOIN table3 ON table1.table3_foreign_key = table3.id INNER JOIN table4 ON table1.post_id = table4.id 
WHERE table3.column3 = '0' AND table1.table3_foreign_key NOT IN (1,18,25,28,30,38,55,84, 31) 
ORDER BY table1.id DESC LIMIT 3;

When using MyIsam the optimization path is correct and the query ends in milliseconds

+------+-------------+--------+--------+-----------------------------------------+---------+---------+---------------------------+------+-------------+
| id   | select_type | table  | type   | possible_keys                           | key     | key_len | ref                       | rows | Extra       |
+------+-------------+--------+--------+-----------------------------------------+---------+---------+---------------------------+------+-------------+
|    1 | SIMPLE      | table1 | index  | PRIMARY,table2_foreign_key,idx_sortable | PRIMARY | 4       | NULL                      |    3 | Using where |
|    1 | SIMPLE      | table2 | eq_ref | PRIMARY                                 | PRIMARY | 4       | table1.table2_foreign_key |    1 |             |
|    1 | SIMPLE      | table4 | eq_ref | PRIMARY                                 | PRIMARY | 4       | table1.id                 |    1 |             |
|    1 | SIMPLE      | table3 | eq_ref | PRIMARY                                 | PRIMARY | 2       | table1.table3_foreign_key |    1 | Using where |
+------+-------------+--------+--------+-------------------------------------+---------+---------+-------------------------------+------+-------------+

but with InnoDb the optimization path is completely wrong: it creates a huge temporary table and sorts it, ignoring that it should be possible to do that only with primary keys and that the result needs only 3 rows. The query lasts hours.

+------+-------------+--------+--------+-----------------------------------------+------------------+---------+---------------------------+---------+----------------------------------------------+
| id   | select_type | table  | type   | possible_keys                           | key              | key_len | ref                       | rows    | Extra                                        |
+------+-------------+--------+--------+-----------------------------------------+------------------+---------+---------------------------+---------+----------------------------------------------+
|    1 | SIMPLE      | table2 | index  | PRIMARY                                 | idx_popular_last | 15      | NULL                      | 5389158 | Using index; Using temporary; Using filesort |
|    1 | SIMPLE      | table1 | ref    | PRIMARY,table2_foreign_key,idx_sortable | post_thread_id   | 4       | table2.id                 |       3 | Using where                                  |
|    1 | SIMPLE      | table4 | eq_ref | PRIMARY                                 | PRIMARY          | 4       | table1.id                 |       1 |                                              |
|    1 | SIMPLE      | table3 | eq_ref | PRIMARY                                 | PRIMARY          | 2       | table1.table3_foreign_key |       1 | Using where                                  |
+------+-------------+--------+--------+-----------------------------------------+------------------+---------+----------------------------+---------+---------------------------------------------+

We've already found different queries with this problem and we solved it using SQL-Hints "force index(PRIMARY)" on table1, but we would like a global solution to fix all possible problematic queries at once. Something like a server configuration to disable temporary tables during SELECT or to bring the behaviour of InnoDB-Optimizer as it was with MyIsam.

Thank you in advance for suggestions

5
  • Try analyze table table1 and others to ensure the stats are correct. There was a few bad query plan choices in the now unmaintained 10.3 version as the ORDER BY .. LIMIT isn't a major part of query planning. I can't tell what idx_popular_last and post_thread_id correspond to.
    – danblack
    Commented Sep 7, 2023 at 20:27
  • analyze table table1 says OK, idx_popular_last is on (table2.column1, table2.column2, table2.column3), post_thread_id is on (table1.table2_foreign_key) Commented Sep 8, 2023 at 10:05
  • Did the query plan change after ANALYZE? You could consider/test a migration to 10.11 for something recent and see if the query plan changes. There are so many (many many) fixes been done since 10.3. ...
    – danblack
    Commented Sep 8, 2023 at 22:01
  • Unfortunately analyze didn't help (I tried it on every table). I will try with a Version update. Thank you Commented Sep 11, 2023 at 8:27
  • Unfortunately the upgrade to latest MariaDB Version doesn't work either. The only workaround is to enhance the queries with SQL-Hints every time a slow query problem is detected Commented Sep 26, 2023 at 8:33

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.