Mysql – MariaDB 5.5 vs MySQL 5.7 query optimizers

I have a query that optimizes differently in MariaDB 5.5.37 and
MySQL 5.0 and MySQL 5.7. When poorly optimized it takes about 400
seconds to run, but on MySQL 5.7 which optimizes it differently,
it runs in about 3 seconds.

I've tried a variety of different versions of MySQL and MariaDB, and
just discovered that MySQL 5.7 optimizes it differently from the others
I have tried.

The query is generated by the Webtrees geneology software.

I'm wondering if anyone here can tease out what causes the different
optimizations and whether it is possible to reorder the query to
force a better query plan.

Here is the table definition:

CREATE TABLE `wt_name` (
  `n_file` int(11) NOT NULL,
  `n_id` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
  `n_num` int(11) NOT NULL,
  `n_type` varchar(15) COLLATE utf8_unicode_ci NOT NULL,
  `n_sort` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `n_full` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `n_surname` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `n_surn` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `n_givn` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `n_soundex_givn_std` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `n_soundex_surn_std` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `n_soundex_givn_dm` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `n_soundex_surn_dm` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`n_id`,`n_file`,`n_num`),
  KEY `ix1` (`n_full`,`n_id`,`n_file`),
  KEY `ix2` (`n_surn`,`n_file`,`n_type`,`n_id`),
  KEY `ix3` (`n_givn`,`n_file`,`n_type`,`n_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

The table has 186,123 rows. Identical data loaded in both databases.

Here is the version info for tha MariaDB 5.5 and MySQL 5.7 servers:

MariaDB> SHOW VARIABLES LIKE "%version%";
+-------------------------+---------------------+
| Variable_name           | Value               |
+-------------------------+---------------------+
| innodb_version          | 5.5.37-MariaDB-34.0 |
| protocol_version        | 10                  |
| version                 | 5.5.37-MariaDB-log  |
| version_comment         | Source distribution |
| version_compile_machine | x86_64              |
| version_compile_os      | Linux               |
+-------------------------+---------------------+

mysql> SHOW VARIABLES LIKE "%version%";
+-------------------------+------------------------------+
| Variable_name           | Value                        |
+-------------------------+------------------------------+
| innodb_version          | 5.7.4                        |
| protocol_version        | 10                           |
| version                 | 5.7.4-m14                    |
| version_comment         | MySQL Community Server (GPL) |
| version_compile_machine | x86_64                       |
| version_compile_os      | Linux                        |
+-------------------------+------------------------------+

The query in question (copied from the slow query log, with some indentation
added for clarity):

SELECT SQL_CACHE n2.n_surn, n1.n_surname, n1.n_id
  FROM `wt_name` n1  JOIN
    (SELECT n_surn, n_file
       FROM `wt_name`
      WHERE n_file=4 AND n_type!='_MARNM' AND
            n_surn NOT IN ('', '@N.N.')
     GROUP BY n_surn COLLATE 'utf8_unicode_ci', n_file
    ) n2
    ON (n1.n_surn=n2.n_surn COLLATE 'utf8_unicode_ci' AND n1.n_file=n2.n_file)
    AND n_type!='_MARNM'

Here is the the explain output on MariaDB (the slow one, typical
query time 400s):

+------+-------------+------------+-------+---------------+------+---------+----
---------------------+--------+-------------------------------------------------
----------+
| id   | select_type | table      | type  | possible_keys | key  | key_len | ref
                     | rows   | Extra
          |
+------+-------------+------------+-------+---------------+------+---------+----
---------------------+--------+-------------------------------------------------
----------+
|    1 | PRIMARY     | n1         | ALL   | ix2           | NULL | NULL    | NUL
L                    | 184314 | Using where
          |
|    1 | PRIMARY     | <derived2> | ref   | key0          | key0 | 4       | hgr
c_webtrees.n1.n_file |     10 | Using where
          |
|    2 | DERIVED     | wt_name    | range | ix2           | ix2  | 768     | NUL
L                    |  92159 | Using where; Using index; Using temporary; Using
 filesort |
+------+-------------+------------+-------+---------------+------+---------+----
---------------------+--------+-------------------------------------------------
----------+

Here is the explain output on MySQL 5.7 (the fast one, typical
query time under 3s):

+----+-------------+------------+------------+-------+---------------+------+---
------+----------------+-------+----------+-------------------------------------
----------------------+
| id | select_type | table      | partitions | type  | possible_keys | key  | ke
y_len | ref            | rows  | filtered | Extra
                      |
+----+-------------+------------+------------+-------+---------------+------+---
------+----------------+-------+----------+-------------------------------------
----------------------+
|  1 | PRIMARY     | <derived2> | NULL       | ALL   | NULL          | NULL | NU
LL    | NULL           | 94067 |   100.00 | NULL
                      |
|  1 | PRIMARY     | n1         | NULL       | ref   | ix2           | ix2  | 77
2     | func,n2.n_file |    19 |   100.00 | Using index condition
                      |
|  2 | DERIVED     | wt_name    | NULL       | range | ix2           | ix2  | 76
8     | NULL           | 94067 |   100.00 | Using where; Using index; Using temp
orary; Using filesort |
+----+-------------+------------+------------+-------+---------------+------+---
------+----------------+-------+----------+-------------------------------------
----------------------+

Here's a description of purpose of the query:

"This query is generated by the "Individual list" page. The page
allows users to list all the individuals in a family tree who have
a particular surname, or initial. It does include an option to list
all surnames or all individuals."

I've tried to provide all of the relevant and useful info. Please let me
know if I've left out anything important. I'd be very grateful if anyone has
any pointers to improving this query.

Thanks,

Mark