--
-- Структура таблицы `cities`
--

CREATE TABLE IF NOT EXISTS `cities` (
  `id` bigint(50) unsigned NOT NULL,
  `title` varchar(250) CHARACTER SET utf8mb4 NOT NULL,
  `area` varchar(250) NOT NULL,
  `region_id` int(250) unsigned DEFAULT NULL,
  `country_code` enum('ru','en','lv','fr','de','tr','ae','ir','es','it','au','nl','be','lu','no','se','ch','ee','dk','pt','lt','gr','fi','pl','bg','cz','sk','at','si','hr','rs','ba','hu') NOT NULL,
  `country_id` int(11) DEFAULT NULL,
  `region` varchar(200) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

--
-- Индексы таблицы `cities`
--
ALTER TABLE `cities`
  ADD UNIQUE KEY `id_2` (`id`,`region_id`,`country_code`),
  ADD KEY `country_code` (`country_code`),
  ADD KEY `region_id` (`region_id`),
  ADD KEY `title` (`title`),
  ADD KEY `id` (`id`),
  ADD KEY `country_id` (`country_id`);

SELECT SQL_NO_CACHE * FROM cities c WHERE c.country_id = 212 AND c.title LIKE 'I%' ORDER BY c.id ASC LIMIT 5

SELECT SQL_NO_CACHE id FROM cities c WHERE c.country_id = 212 AND c.title LIKE 'I%' ORDER BY c.id ASC LIMIT 5

SELECT SQL_NO_CACHE * FROM cities c USE INDEX(search_index) WHERE c.country_id = 212 AND c.title LIKE 'I%' ORDER BY c.id ASC LIMIT 5

mysql  Ver 14.14 Distrib 5.5.44, for debian-linux-gnu (x86_64) using readline 6.3

mysql  Ver 14.14 Distrib 5.5.44, for debian-linux-gnu (x86_64) using readline 6.3

SELECT SQL_NO_CACHE * FROM cities WHERE id IN(SELECT id FROM cities c USE INDEX(search_index) WHERE country_id = 212 AND title LIKE 'I%' ORDER BY id ASC LIMIT 5) 

#1235 - This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery' 

SELECT SQL_NO_CACHE *
FROM cities, (SELECT id FROM cities c USE INDEX(search_index) WHERE country_id = 212 AND title LIKE 'I%' ORDER BY id ASC LIMIT 5) t
WHERE cities.id=t.id

SELECT 
	r.id AS region_id, 
	r.title AS region_title, 
	c.id AS city_id, 
	c.title AS city_title 
FROM 
	cities c, 
	(
		SELECT 
			id 
		FROM 
			cities USE INDEX(search_index) 
		WHERE 
			country_id = 212 
			AND (
				title LIKE 'g%' 
				OR title LIKE 'г%' 
				OR title LIKE 'g%'
			) 
		ORDER BY 
			id ASC 
		LIMIT 
			5
	) t, 
	regions r, 
	countries co 
WHERE 
	c.id = t.id 
	AND r.id = c.region_id 
	AND co.id = c.country_id 
	AND co.country_code = 'ru' 
GROUP BY 
	c.id

SELECT 
	r.id AS region_id, 
	r.title AS region_title, 
	c.id AS city_id, 
	c.title AS city_title 
FROM 
	cities c, 
	(
		SELECT 
			id 
		FROM 
			cities USE INDEX(search_index) 
		WHERE 
			country_id = 212 
			AND (
				title LIKE 'g%' 
				OR title LIKE 'г%' 
				OR title LIKE 'g%'
			) 
		ORDER BY 
			id ASC 
		LIMIT 
			5
	) t, 
	regions r, 
	countries co
--->LEFT JOIN regions r ON r.id = c.region_id<----
WHERE 
	c.id = t.id 
	AND r.id = c.region_id 
	AND co.id = c.country_id 
	AND co.country_code = 'ru' 
GROUP BY 
	c.id