Multiple self Join on Mysql 5.7 - Poor Perfomance I have a table containing a lot of geonames and I am querying it with self join in order to get the cross referenced information.

The only issue is that it takes 7/10 seconds for the query to be executed (and locks the table) representing a bottle neck of the app.

I checked other answers but I could not find out a solution for me. I guess I created all the Index that could help me. I am not an expert in MySql so please if you need more info for a better understand feel free to ask.

This is the query:

select  gp.*,
                        gp1.description as desc_adm1,
                        gp2.description as desc_adm2,
                        gp3.description as desc_adm3,
                        gp4.description as desc_adm4
                 from
                 geonames_table gp left join
                 geonames_table gp1 on gp1.country = gp.country
                                        and gp1.geonames_code = 'ADM1'
                                        and gp1.adm1 = gp.adm1
                                        left join
                 geonames_table gp2 on gp2.country = gp.country
                                        and gp2.geonames_code = 'ADM2'
                                        and gp2.adm2 = gp.adm2
                                        left join
                 geonames_table gp3 on gp3.country = gp.country
                                        and gp3.geonames_code = 'ADM3'
                                        and gp3.adm3 = gp.adm3
                                        left join
                 geonames_table gp4 on gp4.country = gp.country
                                        and gp4.geonames_code = 'ADM4'
                                        and gp4.adm4 = gp.adm4
                 where 1  and LOWER(gp.description) like 'lo%'  limit 10;


And it is launched against this table structure:

id  bigint(20)  NO  PRI     
description varchar(200)    NO  MUL     
alternative_names   varchar(5001)   NO          
country varchar(2)  NO  MUL     
adm1    varchar(20) NO  MUL     
adm2    varchar(80) NO  MUL     
adm3    varchar(20) YES MUL     
adm4    varchar(20) YES MUL     
latitudine  decimal(65,7)   NO          
longitudine decimal(65,7)   NO          
geonames_class  varchar(1)  NO          
geonames_code   varchar(10) NO  MUL 


Thank you for your help in advance.

Here the Show Create Table output:

CREATE TABLE `31_geonames_places` (
   `id` bigint(20) NOT NULL,
   `description` varchar(200) NOT NULL,
   `alternative_names` varchar(5001) NOT NULL,
   `country` varchar(2) NOT NULL,
   `adm1` varchar(20) NOT NULL,
   `adm2` varchar(80) NOT NULL,
   `adm3` varchar(20) DEFAULT NULL,
   `adm4` varchar(20) DEFAULT NULL,
   `latitudine` decimal(65,7) NOT NULL,
   `longitudine` decimal(65,7) NOT NULL,
   `geonames_class` varchar(1) NOT NULL,
   `geonames_code` varchar(10) NOT NULL,
   PRIMARY KEY (`id`),
   KEY `idx_nome_place` (`description`),
   KEY `Sigla_paese_place` (`country`),
   KEY `idx_geonames_code` (`geonames_code`),
   KEY `idx_adm1` (`adm1`),
   KEY `idx_adm2` (`adm2`),
   KEY `idx_adm3` (`adm3`),
   KEY `idx_adm4` (`adm4`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8
You need 4 composite indexes such as

INDEX(country, geonames_code, adm2)


(The columns can be in any order.)

Please provide SHOW CREATE TABLE; my following two comment would be clarified by it.

LOWER(gp.description) like 'lo%' needs for the collation of description to be ..._ci.  If it is, then this would work much better:

`gp.description like 'lo%'`


That is, a "case insensitive" collation obviates the need for LOWER().

If you don't have an index starting with description, add such.

For lat/lng, decimal(65,7) is much too large -- it takes 33 bytes!  This discusses much smaller alternatives.