Mysql. Многократный JOIN с одной и той же таблицей

Question

[Sergey Nitka]

Прошу помощи так как не знаю как правильно поступить.
Есть к примеру таблица
id | fk_1 | fk_2 | fk_3 | fk_4 | fk_5 | fk_6 | fk_7

И есть таблица на которую ссылаются fk_x
id | name

До поры до времени в fk_1 fk_2 и так далее хранился тупо json типа {id: 1, name: 'name'}
С одной стороны это как не странно удобно, так как когда я получаю все записи в массив я могу достучатся до значения fk_x. например
$name = $data['fk_1']['name'] ;

Фишка в том что fk_1 всегда имеет значение, а остальные не всегда

После того как я убрал JSON получается запрос

SELECT * FROM main_table as `mt`
LEFT JOIN second_table as `st_1` ON `st_1`.id = `mt`.fk_1
LEFT JOIN second_table as `st_2` ON `st_2`.id = `mt`.fk_2
LEFT JOIN second_table as `st_3` ON `st_3`.id = `mt`.fk_3
LEFT JOIN second_table as `st_4` ON `st_4`.id = `mt`.fk_4
LEFT JOIN second_table as `st_5` ON `st_5`.id = `mt`.fk_5
LEFT JOIN second_table as `st_6` ON `st_6`.id = `mt`.fk_6
LEFT JOIN second_table as `st_7` ON `st_7`.id = `mt`.fk_7

Начал тестить на 100к записях два варианта

Как не странно аналайзер мне сказал что с json запрос на выборку 100к записей работает быстрее, чем вариант c join.

Подумал и решил потестировать со всеми преобразованиями в коде, так как в случае с JSON для каждой записи в коде вызывается json_decode, а во втором случае преобразования через алиасы.

Для обратной совместимости пришлось писать в цикле для каждого джоина "алиас-префикс"

SELECT main_table.*, second_table.name as `fk_1_name`
LEFT JOIN second_table as `st_1` ON `st_1`.id = `mt`.fk_1
......

А потом в цикле преобразовать, чтобы можно было достучатся в формате
$data['fk_1']['name'] = $data['fk_1_name']

Результат показал на удивление что вариант с JSON выполняется быстрее как с преобразованиями так и без.

Подскажите, что нужно делать в таком случае. Догадываюсь что модель неправильно составлена изначально в связи с тем, что связь многие ко многим.

Извиняюсь за ошибки и пунктуацию...

Пример привел абстрактный! Вообще главная таблица - это таблица заказа трансфера
где fk_1 - Откуда заказ,
fk_2 - Куда заказ
fk_3 - Промежуточная точка 1 и так далее

Answer 1

[Rsa97]

Лучше использовать хранение в виде `id_zakaza`, `id_fk`, `order` - соответственно ID заказа, ID точки, порядковый номер точки в маршруте. В этом случае JOIN будет всего один.

Comments

[Sergey Nitka]

во первых будет как минимум 2 joina. и в такой выборке будут дублироваться строки
order_id col1 col2 ... colN place_name
1 val1 val2 ... valN place_name_from
1 val1 val2 ... valN place_name_to
1 val1 val2 ... valN place_name_way1

чего мне не нужно....

в таком случае мне нужно будет делать два отдельных запроса
1. получить все айди точки в заказе
2. получить все точки
3. в коде все ето соединить между собой

Вроде как и правильный подход. Many to many. Но тут какой то не полноценный many to many, мы знаем что кол-во FK ограничено

За ответ спасибо.. думал над етим

[Rsa97]

Да, получать придётся двумя запросами, сначала сам заказ, потом маршрут к нему. Один JOIN будет именно во втором запросе.

[Sergey Nitka]

такой вариант не подходит с json работает намного быстрее...

[Sergey Nitka]

разве что результат выборки с двумя джоинами групировать GROUP BY order_id
а на лету генерировать JSON www.thomasfrank.se/mysql_to_json.html

[Rsa97]

Да, но представьте, что принято решение о переименовании какой-либо маршрутной точки. Как будет выглядеть запрос c json в этом случае.
Нормализация применяется не для ускорения работы, а для устранения потенциальных проблем с целостностью данных. В реальных системах, уже по результатам тестирования, в узких местах делают денормализацию для ускорения.

[Sergey Nitka]

тут не в денормализации дело.
жыл был json
откуда - {lat:50,lng:30,name:'name'}
куда - {lat:50,lng:30,name:'name'}
промежуточные - [{lat:50,lng:30,name:'name'},{lat:50,lng:30,name:'name'}]

вот так хранилось изначально.

я подумал и решил что плохо РСУБД пытаться применять как объектную бд хотя все на это сводиться да и удобно его хранить в таком виде.

сразу три проблемы
- скорость выборки (выборка происходит очень часто да еще и такая выборка где нужно получить координаты и название всех точек которые были созданы)
- fulltext поиск (1 - делать индекс колонку с json накладно для бд, 2. даже если и искать кирилица при кодировании превращается в \uXXXX)
- на будущее когда будет своя база адресов (сейчас я сохраняю туда format_address и координаты от геокодера)
- дублирются данные

в моем случае JSON в основном константа. редко нужно будет что-то менять. а даже если и нужно будет изменить маршрутную точку тогда как в MONGODB возьмем да и запишем новый JSON.

вариант в самом вопросе (много FK) не канает так как много джоинов + запрос выполняется медленнее на порядок чем с json (скорее всего из за того что из каждого джоина мы получаем все столбы и их получается не реально много) + кодом нужно формировать вложенные объекты/массивы для удобства доступа

вариант правильный по бест практик тоже плохо будет отрабатывать...(да 2 джоина уже хорошо) но при таком джоине и при выборке 1000 заказов мы получаем результирующие 3000 (если стабильно у половины будет одна промежуточная а если будет 2 или вообще 4) . Помимо этого нам опять в коде разделить точки от заказов (дублирующие удалить) и по айди связать все и кодом создать вложенные объекты

третий варинг с бест практик но с одним но

SELECT o.*,

CONCAT('[',
GROUP_CONCAT(

CONCAT('{ \"id\" :\"',p.id,'\"'),
CONCAT(', \"name\" : \"',p.name,'\"'),
CONCAT(', \"latitude\" : \"',p.latitude,'\"'),
CONCAT(', \"longitude\" : \"',p.longitude,'\"}')

ORDER BY op.position SEPARATOR ', '
)
,']'
) as places

FROM orders_test_2 as o
LEFT JOIN order_places as op
ON o.id = op.order_id
LEFT JOIN places as p
ON p.id = op.point_id
GROUP BY o.id

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

Answer 2

[Vladimir Yakimov]

Мы на практике получали лучшие результаты при использовании подзапросов вместо множественных JOIN. (в сравнении с многократным JOIN с одной и той же таблицей)

Comments

[Sergey Nitka]

Пример запроса такого можно на моем примере?