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?
Resolved the issue by rewriting INNER JOIN to STRAIGHT_JOIN. This forced both slaves to execute query the same way.