SELECT title_ru title_en geoname_id cc id cc geoname_id cc title_ru cc

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
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