Господа, есть у меня иерархический каталог ссылок.

То есть одна таблица в которой записи типа:
запись 1:  id=1  caption="ул.Иванова" parent="г. Мухосранск"
запись 2:  id=2  caption="дом 1"          parent="1"
запись 3:  id=3  caption="дом 2"          parent="1"
запись 4:  id=4  caption="кв.4"            parent="3"

Структура (иерархия) строится по взаимосвязям id и parent, то есть, если я хочу узнать список домов на улице Иванова, я ищу все записи у которых id=1, если меня интересуют номера квартир в доме 2, я ищу все записи у которых id=3 и так далее.


Теперь вопрос - я хочу получить список всех квартир на улице Иванова.
МОжно решить эту задачу формально - получить массив из домов, и каждый элемент этого массива обрабатывать  запрашивая по очереди список квартир, но это накладно.

Как мне одним запросом в MySQL получить все квартиры на улице Иванова?

Будет у вас список из 100 квартир № 1, что будете с ним делать?

Добавлено через 4 минуты и 38 секунд
Вот вместе с домами:

1. считать, сколько всего квартир.
2. выбирать случайную квартиру, пофиг из какого дома, главное чтобы была с улицы Ивнова.

zheka1980, так вам мой вариант с выводом квартир и домов подходит или оставить только квартиры?

Пока не испытывал. НИчего убирать не надо, попробую сам разобраться.
Спасибо за помощь.

Уточню задачу. Видимо без вашей помощи не обойдусь.
Дело в том, что квартиры у меня в отдельной таблице "flats", в этой таблице есть автоинкрементное поле id, и parent, привязывающий к записи номера дома в таблице town (города и улицы находятся в таблице "town")


В общем задачи две, можно, и даже нужно каждую из них решить отдельным запросом.
1. На входе имеем id улицы Иванова, то есть 1, это будет входным параметром, вычисляемым в php скрипте, и передаваемым в функцию.
Нужно посчитать сколько в таблице flats имеется записей, у которых parent привязан к тому дому в таблице "town", который в свою очередь привязан к улице Иванова. Но оперировать надо не caption="ул.Иванова", а id, который, как уже сказано, равен 1
2. То же самое, но получить нужно массив из 10 первых записей таблицы "flats", которые я буду сортировать по параметрам. Ну грубо говоря, запрос должен оканчиваться на "ORDER by rank LIMIT 10"

Спасибо.

Это сообщение отредактировал(а) zheka1980 - 21.9.2014, 20:25

Для эффективного решения такого рода задач необходимо перейти на иной способ хранения. Например, на nested set.
Либо пойти на переопределение данных - например, хранить материализованный путь узла (полный адрес).

Нашел статью вроде бы о том, что мне нужно - http://club.shelek.ru/viewart.php?id=307

Там написано - создать таблицу с такими-то параметрами. Я создал.
Дальше предлагается сделать это


Я не понял - это как, целиком скормить MySQL ? Мой сервер подавился этой строкой.

Это сообщение отредактировал(а) zheka1980 - 22.9.2014, 18:45

Эх... пока ждал ответа и, почитывая статейки, тихо ужасался nested set, решил все-таки попробовать подсчитывать рекурсивной функцией на PHP.
То есть, перебираю всех потомков до самого дна (то есть до первого "бездетного" потомка), попутно составляя список этих самых бездетных потомков. 
Получилось около 400 запросов
Далее склеиваю строку типа


Забил таблицу flats мусором из 1500 записей. И выполнил в ней поиск вышеуказанной строкой.
Заняло 0.0042 сек. (Комп - Core2Dou 3 ГГц)



Ответьте на последний вопрос и я от вас отстану: 
В идеале планируется 100 000 записей, элементов каталога 400, вложенность - 3-4, не более, скажите, сканирование 400 запросами  и одним, 401-м, но длиннющим запросом выборка - это будет насилием над MySQL или если работает, то нормально?

Организуй то же на стороне сервера на курсорах - сэкономишь на трафике и уж точно не проиграешь по скорости.

Смотри не вылети за размер пакета. Будет не очень приятно...

Это ты просто не "въехал" в их идеологию. Ренально там всё достаточно просто. Тем более что запросы шаблонные, и их типов немного, шаблоны можно параметризовать и захардкодить - или вообще выволочь на сервере в хранимки.

Можете на пальцах объяснить практический смысл left key и right key?
Исходя из чего они формируются и самое главное, что они дают?



А каков он в среднем?

Добавлено @ 08:31


Секунду.. PHP и MySQL ведь на одном сервере. О каком трафике идет речь?
И что такое курсоры?

Это сообщение отредактировал(а) =Женек= - 23.9.2014, 09:05

В смысл Nested Set въехал. По сути это та же рекурсия, мой скрипт проходит по дереву каталога тот же путь, что и leftkey и rightkey. 
Только у моего скрипта это как-то само получается, а тут все разжевывать надо.
Пытаюсь понять, насколько MySQL будет быстрее с этим справляться.

Хм... а ведь в Nested Sets это будет даже проще и точно быстрее. Нужно просто выбрать записи, где разница между leftkey и rightkey не больше единицы - это и будут концевые, "бездетные" узлы. 
Но этой процедурой мы лишь избавляем MySQL от 400 запросов.
Но еще нужно выбрать в таблице №2 все элементы, принадлежащие одному из 400 идентификаторов, полученных в предыдущем запросе.