Two identical MariaDB slaves, two different execution plans

Question

I have replication on MariaDB 10.11.5. Both slaves have exactly the same my.cnf config, MariaDB binaries and hardware. Both slaves are in sync, all tables have persistent stats generated at the same time:

ANALYZE TABLE `photo-gallery-extra`, `photo-gallery`, `photo` PERSISTENT FOR ALL;

On first slave query is executed using following plan (fast):

EXPLAIN EXTENDED
SELECT
    `p`.*,
    `photo-gallery`.`lid` AS `glid`,
    UNIX_TIMESTAMP(`photo-gallery`.`added`) AS `streamdate`,
    `photo-gallery`.`count`,
    `photo-gallery`.`owner` AS `uid`,
    `photo-gallery`.`views`,
    `photo-gallery`.`rate`,
    `photo-gallery`.`ratecnt`,
    `photo-gallery`.`ratesum`,
    `photo-gallery-extra`.`title` AS `gtitle`
FROM
    `photo-gallery`
INNER JOIN
    `photo-gallery-extra` ON `photo-gallery`.`gid` = `photo-gallery-extra`.`gid`
INNER JOIN (
    SELECT
        `q`.`gid`,
        `r`.`id`,
        `r`.`lid`,
        `r`.`title`,
        `r`.`gif`,
        `r`.`width`,
        `r`.`height`
    FROM
        `photo` AS `q`
    LEFT JOIN
        `photo` AS `r` ON `q`.`id` = `r`.`id`
    WHERE
        `q`.`mod` != 2
    GROUP BY
        `q`.`gid`
) AS `p` ON `photo-gallery`.`gid` = `p`.`gid`
WHERE
    `photo-gallery`.`status` = 1
    AND `photo-gallery`.`moderated` != 2
ORDER BY
    `photo-gallery`.`added` DESC
LIMIT 0, 999;

+------+-----------------+---------------------+--------+--------------------------------------------+---------+---------+---------------------------+------+----------+------------------------------------+
| id   | select_type     | table               | type   | possible_keys                              | key     | key_len | ref                       | rows | filtered | Extra                              |
+------+-----------------+---------------------+--------+--------------------------------------------+---------+---------+---------------------------+------+----------+------------------------------------+
|    1 | PRIMARY         | photo-gallery       | index  | PRIMARY,moderated,status,status_2,status_3 | added   | 5       | NULL                      | 1671 |    59.69 | Using where                        |
|    1 | PRIMARY         | photo-gallery-extra | eq_ref | PRIMARY                                    | PRIMARY | 4       | testdb.photo-gallery.gid | 1    |   100.00 |                                    |
|    1 | PRIMARY         | <derived2>          | ref    | key0                                       | key0    | 4       | testdb.photo-gallery.gid | 2    |   100.00 |                                    |
|    2 | LATERAL DERIVED | q                   | ref    | mod,mod_2,gid                              | gid     | 4       | testdb.photo-gallery.gid | 10   |    69.93 | Using index condition; Using where |
|    2 | LATERAL DERIVED | r                   | eq_ref | PRIMARY                                    | PRIMARY | 4       | testdb.q.id              | 1    |   100.00 |                                    |
+------+-----------------+---------------------+--------+--------------------------------------------+---------+---------+---------------------------+------+----------+------------------------------------+

On second slave the same query is executed using following plan (slow):

EXPLAIN EXTENDED SELECT `p`.*, `photo-gallery`.`lid` AS `glid`, UNIX_TIMESTAMP(`photo-gallery`.`added`) AS `streamdate`, `photo-gallery`.`count`, `photo-gallery`.`owner` AS `uid`, `photo-gallery`.`views`, `photo-gallery`.`rate`, `photo-gallery`.`ratecnt`, `photo-gallery`.`ratesum`, `photo-gallery-extra`.`title` AS `gtitle` FROM `photo-gallery` INNER JOIN `photo-gallery-extra` ON `photo-gallery`.`gid`=`photo-gallery-extra`.`gid` INNER JOIN (SELECT `q`.`gid`, `r`.`id`, `r`.`lid`, `r`.`title`, `r`.`gif`, `r`.`width`, `r`.`height` FROM `photo` AS `q` LEFT JOIN `photo` AS `r` ON `q`.`id`=`r`.`id` WHERE `q`.`mod`!=2 GROUP BY `q`.`gid`) AS `p` ON `photo-gallery`.`gid`=`p`.`gid` WHERE `photo-gallery`.`status`=1 AND `photo-gallery`.`moderated`!=2 ORDER BY `photo-gallery`.`added` DESC LIMIT 0,999;
+------+-----------------+---------------------+--------+--------------------------------------------+---------+---------+---------------------------------+---------+----------+------------------------------------+
| id   | select_type     | table               | type   | possible_keys                              | key     | key_len | ref                             | rows    | filtered | Extra                              |
+------+-----------------+---------------------+--------+--------------------------------------------+---------+---------+---------------------------------+---------+----------+------------------------------------+
|    1 | PRIMARY         | photo-gallery-extra | ALL    | PRIMARY                                    | NULL    | NULL    | NULL                            | 1370470 |   100.00 | Using temporary; Using filesort    |
|    1 | PRIMARY         | photo-gallery       | eq_ref | PRIMARY,moderated,status,status_2,status_3 | PRIMARY | 4       | testdb.photo-gallery-extra.gid | 1       |    29.78 | Using where                        |
|    1 | PRIMARY         | <derived2>          | ref    | key0                                       | key0    | 4       | testdb.photo-gallery-extra.gid | 2       |   100.00 |                                    |
|    2 | LATERAL DERIVED | q                   | ref    | mod,mod_2,gid                              | gid     | 4       | testdb.photo-gallery.gid       | 10      |    69.92 | Using index condition; Using where |
|    2 | LATERAL DERIVED | r                   | eq_ref | PRIMARY                                    | PRIMARY | 4       | testdb.q.id                    | 1       |   100.00 |                                    |
+------+-----------------+---------------------+--------+--------------------------------------------+---------+---------+---------------------------------+---------+----------+------------------------------------+
5 rows in set, 1 warning (0.001 sec)

How can I force the second slave to have the same execution plan as the first slave?

Answer 1

Resolved the issue by rewriting INNER JOIN to STRAIGHT_JOIN. This forced both slaves to execute query the same way.

Answer 2

It's not easy to find the cause. In the first it use an index call 'added' so maybe there is an index which was only created on the first slave.

• You can try an analyze to get more information about that index.
• You can compare all the configurations of both slaves.
• Maybe add an index with status and moderated on photo-gallery table (using the master to replicate in both slaves).