mysql - 一次查询中的MySQL优化

我有几张大约1000万行的桌子。
执行此查询的时间约为10分钟：

SELECT
    c.id,
    CONCAT_WS(', ', country.name, region.name, c.name) AS [text]
FROM city_name AS cn
    INNER JOIN city AS c
        ON c.id = cn.city_id
    INNER JOIN country
        ON country.id = cn.country_id
    INNER JOIN region
        ON region.id = cn.region_id
WHERE cn.name LIKE '%:q%'
GROUP BY cn.city_id
LIMIT 50

但是当我做两个查询时，它的执行时间大约为5秒：
第一个：

SELECT
    city_id
FROM city_name
WHERE name LIKE '%:q%'
GROUP BY city_id
LIMIT 50

第二个：

SELECT
    c.id,
    CONCAT_WS(', ',country.name,region.name,c.name) AS text
FROM city AS c
    INNER JOIN country
        ON country.id = c.country_id
    INNER JOIN region
        ON region.id = region_id
WHERE c.id IN (:ids)

如何将其优化为一个查询？
谢谢。

最佳答案

你试过这样的内部查询吗。

    SELECT
    c.id, CONCAT_WS(', ',country.name,region.name,c.name) AS text
FROM
    city AS c
INNER JOIN
    country ON country.id = c.country_id
INNER JOIN
    region ON region.id = region_id
WHERE
    c.id IN (
        SELECT
            city_id
        FROM
            city_name
        WHERE
            name LIKE '%:q%'
        GROUP BY
            city_id
        LIMIT 50
    )

或者尝试将过滤器cn.name LIKE '%:q%'从where claus移动到ON claus of first JOIN