SELECT c.title_ru, c.title_en, a.geoname_id,
cc.id, cc.geoname_id, cc.title_ru, cc.title_en,
ad.title_ru, ad.title_en
FROM alternames a
INNER JOIN cities c ON c.geoname_id = a.geoname_id
INNER JOIN countries cc ON cc.id = c.country
LEFT JOIN admins ad ON ad.id = c.admin
WHERE MATCH(name) AGAINST('+san*' IN BOOLEAN MODE)
GROUP BY a.geoname_id
ORDER BY MATCH(name) AGAINST('+san*' IN BOOLEAN MODE) DESC
LIMIT 6
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE a fulltext geoname_id,name name 0 1 Using where; Using temporary; Using filesort
1 SIMPLE c ref country,geoname_id geoname_id 5 tripster.a.geoname_id 1 Using where
1 SIMPLE ad ALL None None None None 3620
1 SIMPLE cc eq_ref PRIMARY PRIMARY 8 tripster.c.country 1
Time: 109.751972914
SELECT COUNT(DISTINCT a.geoname_id)
FROM alternames a
INNER JOIN cities c ON c.geoname_id = a.geoname_id
WHERE MATCH(name) AGAINST('+san*' IN BOOLEAN MODE)
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE a fulltext geoname_id,name name 0 1 Using where
1 SIMPLE c ref geoname_id geoname_id 5 tripster.a.geoname_id 1 Using where; Using index
Time: 1.11763811111
SELECT name, lang, geoname_id, preferred, short
FROM alternames
WHERE geoname_id IN ('3168007','3514929','3517618','1689433','2981243','3980605') AND
MATCH(name) AGAINST('+san*' IN BOOLEAN MODE)
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE alternames fulltext geoname_id,name name 0 1 Using where
Time: 1.09590101242