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.
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).
|1||PRIMARY||<derived2>||NULL||ALL||NULL||NULL||NULL||NULL||4||75||Using where; Using temporary; Using filesort|
|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||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||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||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|
- Can this query be rewritten more elegantly/compactly?
- Can the query be further optimised in any of its aspects?
- 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?
(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.)...
aggrderived table is cross-joined with the
reslderived table, then the
aggrcolumns are aggregated while the grouping is done by the
reslcolumns. That makes little sense. Instead, you could aggregate the
aggrrows separately, then cross-join the resulting single row with
resl(obviously you wouldn't need to group anything at that point), see here.