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仍然没有得到索引。请提供帮助和建议。
2条答案
按热度按时间qhhrdooz1#
专注于。。。
请尝试这些并提供
EXPLAIN SELECT ...
还有时机。掷硬币
DISTINCT
,我不认为这是必要的,至少在我的想法2,3。想法1:
INDEX(country_iso, iata_code)
想法2:想法3:
先拿到身份证,再查详细资料
(我想
id
是PRIMARY KEY
如果每张table上都有。)我不知道思想3会不会比思想2快。
(想法#2,3可能会把表格倒过来。)
pgx2nnw82#
mysql不能使用索引进行连接。
您可以在三个连接上使用一个联合(iata、faa、icao各一个)。