Форум MySQL

вот назрел такой вопрос всё собирался написать, есть много сайтов,вот допустим один из которых avtodoska .ru на котором имеется выбор городов, города состоят из три уровня, Страна>Регион>Город

так вот, меня интересует вопрос как они хранятся и как сними потом работать, ведь список то не маленький получается, как я понимаю однозначно они все они хранятся в БД, могу подозревать что также как и обычно дело обстоит с разделами с Parent.
Структура таблици местности: ID Name Parent
где ID - номер записи(страны,региона,города)
Name - назавние
Parent - указываем ID своего родителя(если это страна то Parent=0)

Если это так то в принципе этого дробится не сложно, но вот как лутше и в тоже время чтобы нагрузка была как можно меньше делать выборку допустим объявлений(которые хранятся уже в другой таблице БД) по городам по регионам или отдельно по стране, как в мы должны хранить индефикатор местности которому объявление принадлежит?

Структура таблици объявления: ID Name City
и так, поле "City" у нас должно содержать ID местности, но если мы укажем этому полю ID города а не ID региона или ID страны, то мы сможем сделать выборку только по городу, А как сделать так чтобы выборка велась если мы выбрали не город а Регион то нам нужно сделать выборку по всем городам данного региона, но у нас ведь в поле "City" каждого объявления хранится ID города но не ID региона, это какой запрос нужно делать? я пологаю в запросе можно применить IN(город1,город2,город3,город4...городN), если мы делаем выборку по всему региону, сделав в начале запрос определив все города и их все перечислить в IN() в принципе можно и так поступить, но а если городов много, или к примеру если мы решили сделать выборку по всей Стране, тогда как тут? мы ведь не сможем в IN() сделать перечисление всех регионов и городов принадлежащий всем регионам и стране, список получится огромный.

Можно вроде бы поступить ещё таким образом, в поле "City" хранить не только ID города, но и ID региона и страны, и потом регулярными в запросе их выбирать, примерно так `city` regexp '[[:<:]]($cityid)[[:>:]]'
но применять регулярные в запросе вроде нагрузка на БД увеличивается на сколько незнаю

Да и ещё, на сайте также заметил при выборе города или региона, страны, для разделов присутствует счетчик отображающий кол-во объявлений, какой тут применяется запрос на подсчет?

Если кратко то тут два вопроса, как ПРАВИЛЬНО хранить ID города в поле "City" для объявлений, и как ПРАВИЛЬНО делать выборку?

Много букв ;-)

Структура этих справочников простая.

Страны: id_country | name Регионы: id_region | id_country | name Города id_city | id_region | name

И всё. Теперь можно получить любую информацию в любых комбинациях при любых условиях. Одинаковые поля - ключи связи таблиц.

А вопросы производительности, при необходимости, решаются кешированием.

это если я не ошибаюсь структура таблицы называется вроде "один ко многим", тоже вариант, думаю он получше будет чем я привел с Parent, но это понятно, я имел введу другое, как лутше хранить индефикатор местности в таблицы допустим объявлений, по которому можно было делать выборку, и каким потом запросов их выводить.

Да и ещё, поскольку города в поле таблици объявления находится ID а не прямое название города, к примеру "Новосибирск" и т.п. то както ID нужно конвертировать на Имя, можно конечно сделать функцию, которая будет обращается к БД по ID и по нему определять имя города и его подставлять каждому объявлению, но тогда запросов к БД больше на столько сколько будет выводится записей(объявлений). Както можно это упростить?

Таблица с объявлениями моет иметь примерно такую структуру:

id_ad id_city title content

Теперь, если нам надо, например вытащить все объявления и добавить название города то пишем простой запрос:

SELECT t1.ad,t1.title,t1.content,t2.name as city_name  FROM ad_table t1  LEFT JOIN city_table t2 ON t2.id_city = t1.id_city

И всё, без всяких дополнительных функций.

хм, интересно, надо будет почитать про JOIN, но это если по все записям и с выводом названия города, а что если допустим нужно сделать выборку только по Региону какой запрос должен быть? и какой запрос если выбрать нужно будет по Стране или отдельно по Городу?

Если по конкретному региону то:

SELECT t1.ad,t1.title,t1.content,t2.name as city_name   FROM ad_table t1   LEFT JOIN city_table t2 ON t2.id_city = t1.id_city  WHERE t2.id_region='.$id_region;

По стране (нужно присоединить таблицу с регионами):

SELECT t1.ad,t1.title,t1.content,t2.name as city_name   FROM ad_table t1   LEFT JOIN city_table t2 ON t2.id_city = t1.id_city  LEFT JOIN region_table t3 ON t3.id_region = t2.id_region  WHERE t3.id_country='.$id_country;

спасибо огромное, буду изучать, но я слышал что вроде с JOIN много злоупотреблять нельзя, т.к они много в себе несут нагрузку на БД, так ли это?

да и ещё, если можно конечно, как обычно делается подсчет на кол-во записей, у меня также имеется неограниченная структура разделов через Parent, обычно два-три уровня используется не больше, как можно подсчитать при выборе определенного Города, Региона или Страны, колько записей находится в разделах?

на сайте avtodoska .ru там сразу видно, при выборе города, сразу ведется подсчет записей для каждого раздела, вроде как я понимаю тут заранее не сохранишь счетчик под каждый город, т.к городов очень много, поэтому тут я пологаю нужно почитывать постоянно(без сохранения) при каждом обращении.

как обычно с этим делом справляются? буду очень признателен

Основная польза баз данных как раз и заключается в возможности таких объединений (один ко многим, многие ко многим). И если грамотно использовать индексы, то проблемы с производительностью начнутся очень нескоро. А при наличии сколь-нибудь существенной нагрузки проще докупить доп. сервер, чем оптимизировать каждый байт. (Хотя определённую оптимизацию всё равно надо проводить). А если поставить кеширующую систему, типа memcache, то нагрузку на БД можно будет очень прилично уменьшить (всё зависит от степени "динамизма" сайта).

Но это всё отдалённые перспективы, а пока проект маленький можно не париться за нагрузку, если база грамотно спроектирована.

Подсчёт обычно ведётся в реальном времени при каждом запросе. Т.е например полное количество объявлений из одного города:

'SELECT count(id_ad) AS cnt FROM ad_table WHERE id_city = '.$id_city;

насчет кэширования мне еще далеко, пока с этим бы справится)

вообще я хотел написать универсальный скрипт по городам,регионам и странам, который можно было применить на любую тематику проекта, это может и объявления и автопортал, автосалоны, недвижимость, строительство и т.п. где можно применить города.

И такой еще вопрос, если не трудно, что если изменить структуру таблицы(для объявлений) где было одно поле "city" а теперь сделать с тремя полями: country, region, city и потом по ним делать выборку

но это для того чтобы потом при выборке не использовать кучу JOIN, т.к. не известно какие еще запросы придется делать. Но в тоже время использовать структуру хранения Городов как выше посоветовал "один ко многим". Как думаешь, лутше будет чем использовать JOIN ?

JOIN позволяет избежать избыточности.

Вот смотри. Сделаешь ты, скажем, одну таблицу с тремя полями города, региона и страны.
И теперь при каждом добавлении города надо добавлять и страну и регион.

И теперь прикинь сколько городов в России, например в Московской области. И ты будешь для каждого города добавлять одни и те же поля страны и региона? И получишь таблицу такого объёма, что у тебя база сломается даже на самом просто запросе.

MySQL очень грамотно объединяет таблицы, если они правильно нормированы.

хотя да, верно, я не подумал что много мусора получится. Тут думал что в будущем возможно получится избежать лишних запросов, мало ли, может какиенибудь понадобятся, к примеру определить ID страны или региона по городу, или еще что нибудь, но теперь понял что лутше сделать один лишний запрос чем хранить столько мусора постоянно.

Ну теперь понятно, буду разбираться в этом направлении, за помощь спасибо большое)

кстати вот еще что хотел спросить, тут слышал что проставления индексов тоже играет большую роль н производительность, в даном случаи если структура таблице городов будет такой как ниже, то на какие поля нужно сделать индексы и на все ли эти таблицы требуется ?

Страны: id_country | name Регионы: id_region | id_country | name Города id_city | id_region | name

Ну можно забабахать индексы на id_country и id_region . Ну а так столбцы id_* целочисленные и выборка в них осуществляется очень быстро.

Да и с типом полей id_* не перебарщивайте. Обычно, начинающие, фигачат везде int(11) хотя понятно, что нет в мире миллиарда стран, да и регионов вряд ли столько будет. Поэтому для стран и регионов вполне подойдёт тип tinyint(3), для городов int(6)

понятно, ну то что там целочисленные значения будут то это однозначно, спасибо, буду изучать эту тему)