Mariadb 5.5 slower than MySQL 5.1

Question

I have a query that runs in about 20 seconds on a MySQL 5.1 server but takes almost 15 minutes on a MariaDB 5.5 server. Usual suspects like key_buffer_size and tmp_table_size and max_heap_table_size are all equal (128M). Most settings are equal as far as I can see (query_cache,etc)

The query:

SELECT  products.id, 
concat(publications.company_name,' [',publications.quote,'] ', products.name) as n, 
products.impressions, 
products.contacts, 
is_channel, 
sl.i, 
count(*) 
FROM products 
LEFT JOIN publications ON products.publications_id = publications.id 
LEFT OUTER JOIN (  
    SELECT adspace.id AS i, 
    slots.products_id FROM adspace 
    LEFT JOIN  slots ON adspace.slots_id = slots.id 
        AND adspace.end > '2016-01-25 10:28:49' 
        WHERE adspace.active = 1) AS sl 
    ON sl.products_id = products.id  
WHERE 1 = 1 
AND publications.active=1 
GROUP BY products.id 
ORDER BY n ASC;

The only difference is in the explain fase:

Old server (MySQL 5.1)

+----+-------------+--------------+--------+---------------+---------+---------+-----------------------------------------+--------+---------------------------------+
| id | select_type | table        | type   | possible_keys | key     | key_len | ref                                     | rows   | Extra                           |
+----+-------------+--------------+--------+---------------+---------+---------+-----------------------------------------+--------+---------------------------------+
|  1 | PRIMARY     | products     | ALL    | NULL          | NULL    | NULL    | NULL                                    |   6568 | Using temporary; Using filesort |
|  1 | PRIMARY     | publications | eq_ref | PRIMARY       | PRIMARY | 4       | db.products.publications_id |      1 | Using where                                 |
|  1 | PRIMARY     | <derived2>   | ALL    | NULL          | NULL    | NULL    | NULL                                    |  94478 |                                 |
|  2 | DERIVED     | adspace      | ALL    | NULL          | NULL    | NULL    | NULL                                    | 101454 | Using where                     |
|  2 | DERIVED     | slots        | eq_ref | PRIMARY       | PRIMARY | 4       | db.adspace.slots_id         |      1 |                                             |
+----+-------------+--------------+--------+---------------+---------+---------+-----------------------------------------+--------+---------------------------------+

New server (MariaDB 5.5)

+------+-------------+--------------+--------+---------------+---------+---------+-----------------------------------------+--------+---------------------------------+
| id   | select_type | table        | type   | possible_keys | key     | key_len | ref                                     | rows   | Extra                           |
+------+-------------+--------------+--------+---------------+---------+---------+-----------------------------------------+--------+---------------------------------+
|    1 | SIMPLE      | products     | ALL    | test_idx      | NULL    | NULL    | NULL                                    |   6557 | Using temporary; Using filesort |
|    1 | SIMPLE      | publications | eq_ref | PRIMARY       | PRIMARY | 4       | db.products.publications_id |      1 | Using where                                 |
|    1 | SIMPLE      | adspace      | ALL    | NULL          | NULL    | NULL    | NULL                                    | 100938 | Using where                     |
|    1 | SIMPLE      | slots        | eq_ref | PRIMARY       | PRIMARY | 4       | db.adspace.slots_id         |      1 | Using where                                 |
+------+-------------+--------------+--------+---------------+---------+---------+-----------------------------------------+--------+---------------------------------+

An index was added to the products table on the new server to speed things up, to no avail.

Engine variables:

Old server:

mysql> show variables like '%engine%';
+---------------------------+--------+
| Variable_name             | Value  |
+---------------------------+--------+
| engine_condition_pushdown | ON     |
| storage_engine            | MyISAM |
+---------------------------+--------+

mysql> show variables like '%buffer_pool%';
+-------------------------+---------+
| Variable_name           | Value   |
+-------------------------+---------+
| innodb_buffer_pool_size | 8388608 |
+-------------------------+---------+

New server:

MariaDB [db]> show variables like '%engine%';
+---------------------------+--------+
| Variable_name             | Value  |
+---------------------------+--------+
| default_storage_engine    | InnoDB |
| engine_condition_pushdown | OFF    |
| storage_engine            | InnoDB |
+---------------------------+--------+


MariaDB [db]> show variables like '%buffer_pool%';
+---------------------------------------+-----------+
| Variable_name                         | Value     |
+---------------------------------------+-----------+
| innodb_blocking_buffer_pool_restore   | OFF       |
| innodb_buffer_pool_instances          | 1         |
| innodb_buffer_pool_populate           | OFF       |
| innodb_buffer_pool_restore_at_startup | 0         |
| innodb_buffer_pool_shm_checksum       | ON        |
| innodb_buffer_pool_shm_key            | 0         |
| innodb_buffer_pool_size               | 134217728 |
+---------------------------------------+-----------+

All tables used in the query are MyISAM (both old and new server)

Profiling showed that the old query spend around 16 seconds in 'copying to tmp table' and the new server around 800 seconds in this fase.

New server all has SSD disks for storage and old servers have normal disks.

Edit: I also have a MySQL 5.5 server and there the query only take around 10 seconds. Also with all the same settings as far as I can see.

I tried to summarise it in a table:

Location:       Customer                    Own                     Customer
MySQL Type:     MySQL                       MySQL                   MariaDB
Mysql Version:  5.1.56-community-log        5.5.39-1-log (Debian)   5.5.44-MariaDB-log
HDD:            Normal                      Normal                  SSD
Type:           Virtual                     Real                    Virtual
Query time:     ~15s                        ~10s                    ~15min
DB engine:      MyISAM                      InnoDB                  InnoDB
Table Engine:   MyISAM                      MyISAM                  MyISAM

I don't want to rewrite the query (although it could use some work) but I want to find the difference between the 2 machines, my guess is a setting that isn't ideal in MariaDB but I can't find it.

Answer 1

From the explanation above can be seen that the Derived Table Merge Optimization is used. That unfortunately in your case means that instead of only one full table scan over adspacesome ~6k are done.

A possible solution is to disable the optimization before the query by issuing set optimizer_switch='derived_merge=off';. A backward compatible alternatively would be adding GROUP BY adspace.id, slots.products_id to the subquery (if it does not change the results - safest is grouping over PKs of all joined tables) which forbids the merge by having different semantics.

There is one reported optimizer bug about that - your case might help with it.