Is there anything I can do to optimise this complex query?

Question

I have a rather complex query that spans several layers of subqueries, the two primary ones fetching data from the exact same fields, only one takes the values themselves, the other takes aggregates.

I use most of the values for the sake of creating values I mix to customise the ordering of the results. The results themselves are fetched based on a fulltext search, but to keep query times to acceptable levels I have to limit the inner queries, which cuts out many results that may be relevant, so I'd like to be able to keep this limit to higher values (if it can't be avoided at all).

This is by far the most complex query I've ever pieced together, so I'm by no means an expert, but I reached the limit of the rooms for optimisation I can see and would appreciate any help.

DB Fiddle:

https://dbfiddle.uk/?rdbms=mysql_5.7&fiddle=5742db7124a1dccaff7133d2e2b30b8d

The query:

SELECT MIN(place_id) AS place_id,
       name,
       administration,
       country,
       MAX(relevance) AS relevance
FROM (
    SELECT *,
           (rescale(population, mn_pop, mx_pop)              * 2.4) +
           (rescale(name_relevance, mn_plre, mx_plre)        * 0.0) +
           (rescale(distance, mx_dist, mn_dist)              * 2.2) +  -- inverted
           if(t.country_id = (
               SELECT country_id FROM country_names
               WHERE name = 'Germany'
               LIMIT 1
           ), 0.6, 0)
               / 4
               AS relevance
    FROM (
        SELECT resl.*,
               MIN(aggr.population)             AS mn_pop,
               MAX(aggr.population)             AS mx_pop,
               MIN(aggr.name_relevance)         AS mn_plre,
               MAX(aggr.name_relevance)         AS mx_plre,
               MIN(aggr.distance)               AS mn_dist,
               MAX(aggr.distance)               AS mx_dist
        FROM (
            SELECT p.population,
                   ST_DISTANCE_SPHERE(position, ST_POINTFROMTEXT(ST_ASTEXT(POINT(7.4653, 51.5136)), 4326)) AS distance,
                   MATCH(pn.name) AGAINST('+dor*' IN BOOLEAN MODE)     AS name_relevance
            FROM places p
            JOIN place_names pn ON p.id = pn.place_id
            JOIN admin_names an ON p.admin_id = an.admin_id
            JOIN country_names cn ON p.country_id = cn.country_id
            JOIN languages l ON pn.language_id = l.id AND an.language_id = l.id AND cn.language_id = l.id
            WHERE l.code_3 = 'ENG' AND MATCH(pn.name) AGAINST('+dor*' IN BOOLEAN MODE)
            LIMIT 200
        ) aggr
        JOIN (
            SELECT p.id AS place_id,
                   pn.name AS name,
                   an.name AS administration,
                   an.abbr AS admin_abbr,
                   cn.name AS country,
                   p.population AS population,
                   p.country_id AS country_id,
                   ST_DISTANCE_SPHERE(position, ST_POINTFROMTEXT(ST_ASTEXT(POINT(7.4653, 51.5136)), 4326)) AS distance,
                   MATCH(pn.name) AGAINST('+rom*' IN BOOLEAN MODE)     AS name_relevance
            FROM places p
            JOIN place_names pn ON p.id = pn.place_id
            JOIN admin_names an ON p.admin_id = an.admin_id
            JOIN country_names cn ON p.country_id = cn.country_id
            JOIN languages l ON pn.language_id = l.id AND an.language_id = l.id AND cn.language_id = l.id
            WHERE l.code_3 = 'ENG' AND MATCH(pn.name) AGAINST('+dor*' IN BOOLEAN MODE)
            LIMIT 200
        ) resl
        GROUP BY place_id, resl.name, resl.administration, resl.admin_abbr, resl.country
    ) t
) t2
WHERE place_id is not null
GROUP BY country, administration, admin_abbr, name
ORDER BY relevance DESC;

(I removed parts of the query and columns that work the same way as others that I left in, for brevity. I'm sorry that there's still so much material but as mentioned, it's a complex query.)

As you can see, there are quite a few repeated or very similar lines between the two inner subqueries; that seems quite dirty and it suggests me that there is room for improvement (which may or may not also improve performance).

EXPLAINed:

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	PRIMARY	<derived2>	NULL	ALL	NULL	NULL	NULL	NULL	4	75	Using where; Using temporary; Using filesort
2	DERIVED	<derived4>	NULL	ALL	NULL	NULL	NULL	NULL	4	100	NULL
4	DERIVED	<derived5>	NULL	ALL	NULL	NULL	NULL	NULL	2	100	Using temporary; Using filesort
4	DERIVED	<derived6>	NULL	ALL	NULL	NULL	NULL	NULL	2	100	Using join buffer (Block Nested Loop)
6	DERIVED	l	NULL	const	PRIMARY,languages_code_3_uindex	languages_code_3_uindex	12	const	1	100	Using index
6	DERIVED	pn	NULL	fulltext	place_names_place_id_language_uindex,new_place_names_language_id_fk,place_id,place_name_fulltext	place_name_fulltext	0	const	1	34.97	Using where; Ft_hints: no_ranking
6	DERIVED	p	NULL	eq_ref	PRIMARY,admin_id,country_id	PRIMARY	8	geodb.pn.place_id	1	100	Using where
6	DERIVED	cn	NULL	eq_ref	admin_names_country_id_language_uindex,country_id,language_id	admin_names_country_id_language_uindex	12	geodb.p.country_id,const	1	100	NULL
6	DERIVED	an	NULL	eq_ref	admin_names_admin_id_language_uindex,admin_id,language_id	admin_names_admin_id_language_uindex	12	geodb.p.admin_id,geodb.pn.language_id	1	100	Using index condition
5	DERIVED	l	NULL	const	PRIMARY,languages_code_3_uindex	languages_code_3_uindex	12	const	1	100	Using index
5	DERIVED	pn	NULL	fulltext	place_names_place_id_language_uindex,new_place_names_language_id_fk,place_id,place_name_fulltext	place_name_fulltext	0	const	1	34.97	Using where
5	DERIVED	p	NULL	eq_ref	PRIMARY,admin_id,country_id	PRIMARY	8	geodb.pn.place_id	1	100	Using where
5	DERIVED	cn	NULL	eq_ref	admin_names_country_id_language_uindex,country_id,language_id	admin_names_country_id_language_uindex	12	geodb.p.country_id,const	1	100	Using index
5	DERIVED	an	NULL	eq_ref	admin_names_admin_id_language_uindex,admin_id,language_id	admin_names_admin_id_language_uindex	12	geodb.p.admin_id,geodb.pn.language_id	1	100	Using where; Using index
3	SUBQUERY	country_names	NULL	ref	country_name,country_name_fulltext	country_name	1020	const	1	100	NULL

Summary:

1. Can this query be rewritten more elegantly/compactly?
2. Can the query be further optimised in any of its aspects?
3. Can the query be modified such that the inner LIMIT (in the example, 200) can be removed or increased as much as possible without having such averse effects on performance?

Answer 1

Where practical, measure the time taken for each subquery. Then focus on the slowest subquery.

When a SELECT involve JOINs and a LIMIT (or a GROUP BY), an optimization is to turn it inside out. I am referring to the two derived tables with LIMIT 200. That is rewrite the subquery to find the ids of the 200 row as simply as possible, then JOIN to the other tables.

It is strange to have a LIMIT without an ORDER BY. And, without knowing what table position is in, I can't get more specific.