New answers tagged explain
MySQL already can filter down to 1 row by using a different smaller index (only 4 bytes). | 1 | SIMPLE | alternatename | ref | IDX_8F82EED4E2097D,idx_geoid_lang_pref | IDX_8F82EED4E2097D | 4 | acme_geonames.geoname.geonameid | 1 | Using where So it doesn't need to use the bigger index. Hope this answers your question.
You do not use the isPreferredName in your query. This must be a/the reason why the index is not used. Also, the optimizer decides. It is not because you create an index that it will be used. All depends on the statistics for the table. How many rows? How many different unique values? What is the expected percentage of returned rows?
Top 50 recent answers are included