Большое кол-во данных mysql?

Question

[M L]

Нужно было реализовать систему заполнения страны и города.
Для это нужна была база данных стран и городов.
Вопрос был: "где взять все эти данные?"
Недолго думая решил выкачать их из vk с помощью api.
Написал скрипт. и пошел спать.
С утра встаю и вижу стран: 236 (норм)
Городов: ~4,716,527 на сумму 239.8 мб

Решил протестировать все это дело. Например по запросу Россия скрипт думал 12 сек.

Суть вопроса - "Почему у ВК такой обход намного быстрее?".

У меня у самого есть Варианты:
1. Огромное кол-во серверов, все это обрабатывают на спокойном.
2. Данные распределены по таблицам стран, а не в одной куче (хотя id городов идут по возрастанию всегда). (Russia - 157.000 городов)
3. Из-за скорости собственной базы данных.

Answer 1

[АртемЪ]

Большое кол-во данных mysql?

Где?
Вот это "стран: 236 (норм), Городов: ~4,716,527 на сумму 239.8 мб" - очень малое количество данных, просто смешное.

Решил протестировать все это дело. Например по запросу Россия скрипт думал 12 сек.

Ну если у вас скрипт медленно "думает" значит надо написать нормальный скрипт.
Выборка из такого количества будет занимать доли секунды на самом старинном сервере.
Более того - за 12секунд или около того реально даже вручную выбрать эти данные из БД напечатанной на бумаге.

Comments

[M L]

SELECT * FROM `cities` WHERE `country_id` = 1
12 сек. Вернет 157.000 городов россии

[M L]

Поправочка. Я же забыл указать сколько городов для страны я возвращаю.
Тогда переформулирую. Да запрос доли. секундный никто не спорит.
Если изменить его на SELECT * FROM `cities` WHERE `country_id` = 1 LIMIT 1;

А если вернуть все города? Или так должно быть?

[АртемЪ]

staffID: Во первых - как замеряли? Что значит вернет?
Во вторых в Росссии чуть больше тысячи городов.

[M L]

АртемЪ: там регионы и т.д и т.п (Сами посмотрите).

https://api.vk.com/method/database.getCities?count...
(только двигайте offset, т.к. макс. возвращаемые значения 1000)

Замерял в консоли простым sql запросом.

[АртемЪ]

staffID: Вы спрашиваете почему у ВК такой подход быстрее.
Где конкретно в ВК делается выборка всех городов? Я такого не встречал, для чего это может понадобиться?
А в базе у вас не города, а населенные пункты.

[M L]

Сама реализация.

Две таблицы:
countries и cities.
в countries- 236 строк, cities - 5 млн. строк

Нужен запрос который вернет все города для одной страны.
в таблицы cities есть доп. поле country_id в нем id страны.

Таким образом SELECT * FROM `cities` WHERE `country_id` = 1
Занимает уйму времени. Однако если запрос в кэше, то мгновенный.

[M L]

АртемЪ: "А в базе у вас не города, а населенные пункты." - да

[M L]

АртемЪ: даже если лимит поставить 150, все равно около 2 сек.

[M L]

АртемЪ: у меня есть ощущения, что каждый город у них в кэше просто.

[M L]

staffID: вернее страна.

[АртемЪ]

Вообще скорость выборки зависит от того как вы залили эти данные в БД, как проиндексировали, и как выбираете.

Чаще всего используется два варианта -
1)Выбираете страну - вам показывают список районов.
Выбираете район- вам показывают список населенных пунктов района.
Выбираете населенный пункт.

2)Выбираете страну - вам показывают пустое поле, или небольшой список крупных городов.
Набираете первую букву - вам выводят список всех нас. пунктов на эту букву
Набираете вторую букву - список сокращается.

[АртемЪ]

Скорость выполнения запроса будет зависеть от кучи факторов.
Вы просто не оптимально выбираете, или проиндексировано не то, что вам нужно.

Вам вообще что нужно в итоге?

[M L]

АртемЪ: Я выбрал страну получил в переменную все города и населенные пункты этой страны.
Потом в инпут ввожу город и получаю подсказки. Только они уже должны быть на клиенте в объекте. А js делает поиск по городу

Например я ввел в инпут слово "мос" и получил кучу подсказок возможных городов и нас. пунктов.

[ThunderCat]

staffID: автокомплит в правильной реализации редко пользуется загрузкой всех возможных вариантов на клиент. Используй аякс, Люк!

Answer 2

[Дмитрий Энтелис]

Режим телепата:
1) у Вас просто нет индекса в таблице cities по полю country_id.
Покажите результат выполнения show create table cities;
2) если mysql запущен с дефолтными конфигами - ему тупо не хватает памяти.

Comments

[M L]

Вы пишите ответы с западнаием, когда решение уже найдено - 'Зачем?'

[Дмитрий Энтелис]

staffID: я не вижу в списке ответов решения.
В чем была причина?

Answer 3

[Paul Denisevich]

Индексы не пробовали ставить? У меня по 15 млн записей выборка за 0.03 сек.

Кроме того, это НЕ большое кол-во данных, это почти ничего. Большое - это 50 петабайт, например.

Comments

[M L]

Вы пишите ответы с западнаием, когда решение уже найдено - 'Зачем?'

[Paul Denisevich]

Думаю, ничего страшного в этом нет.

Answer 4

[Виталий]

fias.nalog.ru Если конечно тебе нужны данные именно по РФ.

Comments

[M L]

Где ты в моем вопросе увидел, что я ищу данные?

[Виталий]

staffID: этого вопроса я не видел, однако, меня смутило, что выдираешь города через контакт и скинул ссылку на ресурс по РФ, который может сильно упростить жизнь. На счет индексов писать еще раз было бы глупо, а умалчивать полезный ресурс, это жлобство.

[M L]

vshvydky:
На, дарю.
P.S. Не всегда база можно обойтись, только базой РФ. Плюс. Иметь 2 098 000 населенных пунктов это не плохо. Гемерой это когда пользователь живет в Украине или Казахстане или вообще США пытается зарегистрироваться и указать страну и город. А у него только Россия перед глазами. Разница есть.

set_time_limit( 0 );
mb_internal_encoding( 'UTF-8' );



function curl( $url ) {
    $ch = curl_init( $url );
    curl_setopt( $ch, CURLOPT_RETURNTRANSFER, true );
    curl_setopt( $ch, CURLOPT_SSL_VERIFYHOST, false );
    curl_setopt( $ch, CURLOPT_SSL_VERIFYPEER, false );
    $response = curl_exec( $ch );
    curl_close( $ch );
    return $response;    
}

$countries = json_decode( curl( 'https://api.vk.com/method/database.getCountries?need_all=1&count=1000&offset=0' ) );
$real_id_c = array();
foreach( $countries->response as $country ) {
    $real_id_c[] = array( 'vk_id' => $country->cid, 'id' => country( $country->title )[ 0 ]->ID );
}
$limit = 200000;
foreach( $real_id_c as $ids ) {
    for( $o = 0; $o <= $limit; $o += 1000 ) {
        $cities = json_decode( curl( 'https://api.vk.com/method/database.getCities?country_id=' . $ids[ 'vk_id' ] . '&count=1000&need_all=1&offset=' . $o ) );
        if ( empty( $cities->response ) ) {
            break;
        }
        foreach( ( array ) $cities->response as $city ) {
            $region = $city->region;
        
        
            if ( empty( $city->region ) )
                $region = '';
            print_r( $city );
        }
    }
}

[Виталий]

staffID: случаем не твой ресурс? citieslist.ru

[M L]

vshvydky: Нет, там населенных пунктов меньше в 2 раза и не хватает 6 стран.

[Виталий]

staffID: самое то для ленивых скинуть бекап на бд