Dmitry Korol Dmitry Korol - 2 months ago 6
MySQL Question

Mysql optimisation in one query

I have some tables with ~10 millions rows.
Time of execution this query is about ~10 minutes:

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


But when i'm making two queries its executions about 5 seconds:

First one:

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


Second one:

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)


How can I optimize it into one query?

Thanks.

Answer

Have you tried inner query like this.

    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
    )

Or try moving the filter cn.name LIKE '%:q%' from where claus to ON claus of first JOIN