mysql索引建议

vu8f3i0k 于 2021-06-20 发布在 Mysql

关注(0)|答案(2)|浏览(397)

select distinct g4_airport.id              as g4airportidentifier,
            airport.id                 as airportidentifier,
            airport.iata_code          as airportiata,
            airport.ident              as airporticao,
            airport.local_code         as airportfaa,
            airport.lastupdated        as lastupdated,
            g4_airport.last_updated    as lastupdatedg4,
            airport.name               as airportname,
            airport.municipality       as cityname,
            airport.latitude           as latitude,
            airport.longitude          as longitude,
            airport.region_iso         as regioniso,
            airport.country_iso        as countryiso,
            airport.timezone           as timezonename,
            g4_airport.is_fuel         as isfuel,
            g4_airport.use_tsa_scanner as isscanner,
            g4_airport.is_station      as isstation,
            g4_airport.is_charter      as ischarter,
            g4_airport.min_connection  as minconnectiontime,
            g4_airport.max_connection  as maxconnectiontime,
            g4_airport.min_turn        as minturn,
            g4_airport.rp_turn         as rpturn,
            g4_airport.acars_active    as isacarsactive,
            g4_airport.code_type       as codetype
from   airports.g4_airport
   join airports.airport
     on g4_airport.code = case g4_airport.code_type
                            when 'iata' then airport.iata_code
                            when 'faa' then airport.local_code
                            when 'icao' then airport.ident
                          end
where  airport.country_iso = 'us'
order  by airport.iata_code;

当我试图对case和join中提到的所有列创建索引时，查询运行了11秒。mysql仍然没有得到索引。请提供帮助和建议。

mysql indexing

来源：https://stackoverflow.com/questions/51330612/mysql-index-suggestion

2条答案

按热度按时间

qhhrdooz1#

专注于。。。

SELECT  ...
    from  g4_airport AS g4
    join     airport AS a
         ON g4.code = case g4.code_type
             when 'iata' then a.iata_code
             when 'faa'  then a.local_code
             when 'icao' then a.ident   end
    where  a.country_iso = 'us'
    order by  a.iata_code;

请尝试这些并提供 EXPLAIN SELECT ... 还有时机。
掷硬币 DISTINCT ，我不认为这是必要的，至少在我的想法2，3。
想法1： INDEX(country_iso, iata_code) 想法2：

( SELECT  ...
    from  g4_airport AS g4
    join     airport AS a
         ON g4.code_type = 'iata'
        AND g4.code = a.iata_code
    where  a.country_iso = 'us'
    order by  a.iata_code;
) UNION ALL
( SELECT  ...
    from  g4_airport AS g4
    join     airport AS a
         ON g4.code_type = 'faa'
        AND g4.code = a.local_code
    where  a.country_iso = 'us'
)
) UNION ALL
( SELECT  ...
    ...  icao...ident
)    
ORDER BY  a.iata_code;

airport:     INDEX(country_iso)   -- although it may not be used
g4_airport:  INDEX(code_type, code)  -- for each `JOIN`

想法3：
先拿到身份证，再查详细资料

SELECT a..., g4...        -- the various columns desired
    FROM
    (      -- start of UNION
        ( SELECT  a.id AS aid, g4.id AS g4id   -- only the PKs
            from  g4_airport AS g4
            join     airport AS a
                 ON g4.code_type = 'iata'
                AND g4.code = a.iata_code
            where  a.country_iso = 'us'
            order by  a.iata_code;
        ) UNION ALL
        ( SELECT  a.id AS aid, g4.id AS g4id
            from  g4_airport AS g4
            join     airport AS a
                 ON g4.code_type = 'faa'
                AND g4.code = a.local_code
            where  a.country_iso = 'us'
        )
        ) UNION ALL
        ( SELECT  ...
            ...  icao...ident
        )
    ) AS u       -- end of the UNION
    JOIN    airport AS a  WHERE  a.id = u.aid
    JOIN ga_airport AS g4 WHERE g4.id = u.g4id
    ORDER BY  u.iata_code;

airport:     INDEX(country_iso)   -- although it may not be used
g4_airport:  INDEX(code_type, code)  -- for each `JOIN`

（我想 id 是 PRIMARY KEY 如果每张table上都有。）
我不知道思想3会不会比思想2快。
（想法#2,3可能会把表格倒过来。）

赞(0）回复(0）举报 2021-06-20

pgx2nnw82#

mysql不能使用索引进行连接。
您可以在三个连接上使用一个联合（iata、faa、icao各一个）。

赞(0）回复(0）举报 2021-06-20