1

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?
9
  • 2
    Can you please provide a working fiddle - I tried with your DDL/DML above and got this... Also, it would probably be best if you included all of the query and relevant data - cf. (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.)...
    – Vérace
    Jul 6 at 10:14
  • @Vérace You appeared to have skipped a couple tables from the creation script; the order I put them in should satisfy all constraints. The columns I removed from the query are still there in the tables I provided, I just don't select them in the query, but they aren't used in WHERE clauses or anywhere else anyway. I'll prepare a fiddle.
    – theberzi
    Jul 6 at 12:14
  • @Vérace I added a working dbfiddle to the question. However, the query is returning nothing there but I guarantee that on my database it does return some items. I'm not quite sure what could cause the difference.
    – theberzi
    Jul 6 at 12:26
  • Nevermind, I had forgotten the data for one of the tables, I'm so sorry. the fiddle is updated and works now.
    – theberzi
    Jul 6 at 12:32
  • 1
    Don't how much actual impact this makes, but one thing particularly stands out to me as redundant. The aggr derived table is cross-joined with the resl derived table, then the aggr columns are aggregated while the grouping is done by the resl columns. That makes little sense. Instead, you could aggregate the aggr rows separately, then cross-join the resulting single row with resl (obviously you wouldn't need to group anything at that point), see here.
    – Andriy M
    Jul 14 at 12:17
0

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.

2
  • position is in table places, as the creation script shows.
    – theberzi
    Jul 13 at 10:37
  • My issue with your suggestion is that I need the joins to find the IDs in the first place, because I need to restrict by language and then match name, administration etc, which all sit in different tables. Furthermore, I need both the records themselves and some aggregates (each one of the two inner queries); even if I got the IDs with a further subquery I would then have to copy this subquery twice to get both sets of results, right? The reason I have a LIMIT without an ORDER BY is that ordering all the results in the innermost subqueries takes a lot of time.
    – theberzi
    Jul 13 at 10:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.