How to store and select cities in the Database?

Question

MySQL

There are three tables loc_name and location and type
In loc_name there are two fields NAME and LOC_ID
Example:
-----NAME------|----LOC_ID---|---
----Moscow--- --|-----21--------|---
----Mos. Region---|-----22--------|---
--Dudino village-|-----29--------|---
And so on ... Everything is stored in it, both cities and villages and regions and districts ...
In another location table There are fields:
PARENT_ID and TYPE_ID and ID
And types are stored in the type table (City, Region, Village, District)
Now I do this, I have a loc_name table associated with location
For example, a person is looking for Dudino, then I select all similar ones from loc_name
And see if their type is a city or a village, then I take it. But I need their full data, so I run an array with the necessary cities and villages through a recursive function, so that from the loc_name table , using the connection with the location table , find areas and so on, up to the last parent.
Everything works correctly, for example, if I do a search and a result of 30, then about 200 requests are obtained, because for example, I entered Mos And, accordingly, the selection of% Mos%
How to properly organize such storage? I thought I could create just one table where everything will already be laid out? Type Name Region District, etc. Then there is no need to do recursion, but then there will be one healthy table and heavy.
Here's how I'm doing it now:

// Тут сам запрос
$city = strip_tags($request->input('city'));
//Здесь я проверяю в связанной таблице на тип, и если это город или село то выбераю
        $loc_id = LocName::where('NAME', 'like', '%'.$city.'%')->with(['location' => function ($query)
        {
            $query->where('TYPE_ID', '>', '4');
        }])->take(50)->orderBy('NAME', 'desc')->get();

        $all = array();

// Эта функция рекурсивно ищет полное название
// например: Дудино, Щелковский район, Московская область, Россия
        function get_parent($id, $path)
        {
// Получаю название на русском и сразу со связью что бы проверить тип
//и если это еще не Страна (страна это главные родитель) то добавляем название в массив и ищем далее
            $par_ar = LocName::where('LANGUAGE_ID', 'ru')->with('location')->where('LOCATION_ID', $id)->first();
            $path[] = $par_ar->NAME;
            if($par_ar->location->TYPE_ID > 1)
            {
                return get_parent($par_ar->location->PARENT_ID, $path);
            }
// Если это страна то отдаем готовый массив для одного города или села.
            return $path;
        }

        foreach ($loc_id as $id)
        {
            $id = $id->LOCATION_ID;
// тут собираем все в один
            $all[$id] = get_parent($id, $path = array());
        }
// Ну и тут я готовый массив <b>$all</b> с городами и селами уже во вьюху кидаю.

Arrays of this kind, as needed, everything works:

6034 => array [
    0 => "Швейник-ПШО Москва снт"
    1 => "Котово деревня"
    2 => "Наро-Фоминский район"
    3 => "Московская область"
    4 => "Россия"
  ]
  8330 => array [
    0 => "Сигнал 74 км ш.Москва-Нижний Новгород снт"
    1 => "Кузнецы деревня"
    2 => "Павлово-Посадский район"
    3 => "Московская область"
    4 => "Россия"
  ]

But there are just a lot of requests: Here the debugger shows:

select * from `loc_name` where `NAME` like '%москва%' order by `NAME` desc limit 50
1.76s
select * from `location` where `location`.`ID` in ('19', '6034', '6454', '7297', '8004', '8008', '8176', '8330', '10291', '10294', '11162', '13486', '55020', '55336', '71509', '72732', '110555', '140372', '148382', '169991', '214864') and `TYPE_ID` > '4'
1.97ms
select * from `loc_name` where `LANGUAGE_ID` = 'ru' and `LOCATION_ID` = '6034' limit 1
990μs
select * from `location` where `location`.`ID` in ('6034')
670μs
select * from `loc_name` where `LANGUAGE_ID` = 'ru' and `LOCATION_ID` = '6030' limit 1
870μs
select * from `location` where `location`.`ID` in ('6030')
740μs
select * from `loc_name` where `LANGUAGE_ID` = 'ru' and `LOCATION_ID` = '5939' limit 1
960μs
select * from `location` where `location`.`ID` in ('5939')
750μs
select * from `loc_name` where `LANGUAGE_ID` = 'ru' and `LOCATION_ID` = '2' limit 1
730μs
select * from `location` where `location`.`ID` in ('2')
И так далее... Еще где то 170 запросов...

And there are about 200 such requests!!!
How to do it right?

Answer 1

CREATE TABLE `geo_cities` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `country_id` int(10) unsigned NOT NULL DEFAULT '0',
  `region_id` int(10) unsigned NOT NULL DEFAULT '0',
  `name` varchar(128) NOT NULL DEFAULT '',
  `ordering` int(11) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `name` (`name`),
  KEY `country_id` (`country_id`),
  KEY `region_id` (`region_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

CREATE TABLE `geo_regions` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `country_id` int(10) unsigned NOT NULL DEFAULT '0',
  `name` varchar(64) NOT NULL DEFAULT '',
  `ordering` int(11) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `country_id` (`country_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

CREATE TABLE `geo_countries` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(128) NOT NULL DEFAULT '',
  `alpha2` char(2) NOT NULL DEFAULT '',
  `alpha3` char(3) NOT NULL DEFAULT '',
  `iso` int(11) NOT NULL DEFAULT '0',
  `ordering` int(11) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `name` (`name`),
  KEY `alpha2` (`alpha2`),
  KEY `alpha3` (`alpha3`),
  KEY `iso` (`iso`),
  KEY `ordering` (`ordering`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

Further related selects (HTML select tag):
first select - list of countries;
chose a country - uploaded a list of regions;
chose a region - loaded a list of cities;
chose a city.
For convenient selection in selects, you can use this for example .
To quickly determine the region -> country by city (by its full name), make a selection from the geo_cities table, connecting it with the rest - there are connecting fields.

Answer 2

Change the storage structure to Nested Set , then all parents can be selected with a single request.