огромная разница в выполнении идетничных запросов, в чём причина?

[Лысый]

огромная разница в выполнении идетничных запросов, в чём причина?

есть два запроса

SELECT users1.userid, users2.user_id
FROM users2
LEFT JOIN users1 ON ( users1.login = concat( 'ge_', users2.name ) )

SELECT users1.userid, users2.user_id
FROM users1
LEFT JOIN users2 ON ( users1.login = concat( 'ge_', users2.name ) )

над двумя таблицами
users1 - 50 тыс записей, users1.login - UNIQUE
users2 - 30 тыс записей, users2.name - UNIQUE

первый запрос моментально выдаёт 233 записи
второй - вешает Мускул так, что приходится снимать задачу


в чём кроется различие?

 

[confguru]

explain тебе подскажет

 

[Лысый]

эх.. если бы так просто - я б не спрашивал

эксплеин даёт

id | select_type | table | type | possible_keys | key | key_len | ref rows | Extra |
1 | SIMPLE |users1 | ALL | NULL |NULL |NULL | NULL| 49858
1 |SIMPLE |users2 | ALL | NULL |NULL |NULL | NULL| 27290

т.е. ничего

 

[akxxiv]

Вопрос на засыпку: зачем во втором запросе джойнить users2, коли ты из нее ни чего не выбираешь. Это во-первых, а во-вторых, Почему ты при левом джойне присоединяемую таблицу пишешь справа?

 

[Лысый]

Автор оригинала: akxxiv
Вопрос на засыпку: зачем во втором запросе джойнить users2, коли ты из нее ни чего не выбираешь. Это во-первых, а во-вторых, Почему ты при левом джойне присоединяемую таблицу пишешь справа?

1) это описка - поля селекта одни и те же
2) что значит "справа"?
как раз суть вопроса в том, что возникает огромная необъяснимая разница, джоинить одно к другому или наоборот.

 

[akxxiv]

SELECT users1.userid, users2.user_id
FROM users1
LEFT JOIN users2 ON ( users1.login = concat( 'ge_', users2.name ) )

Если мы лефт джоним u2 к u1, то в ON должно быть u2.field = u1.field, а не наоборот. Иначе это райт джойн

 

[Лысый]

akxxiv

1) тормозит как раз "правильный" с твоей точки зрения запрос №2
2) ты в серьёз считаешь что от перемены месть операндов что-то зависит? жаль

 

[akxxiv]

Второй как раз не правильный. Он должен быть таким

SELECT users1.userid, users2.user_id
FROM users1
LEFT JOIN users2 ON ( users2.login = concat( 'ge_', users1.name ) )

Или что-то в этом роде. Прочитай про LEFT JOIN & RIGHT JOIN

-~{}~ 01.08.06 13:31:

2) ты в серьёз считаешь что от перемены месть операндов что-то зависит? жаль

Да я так считаю.

 

[4m@t!c]

Эксплейн обоих запросов выложите.

 

[Лысый]

Автор оригинала: akxxiv
Да я так считаю.

полнейший бред

right или left определяется самой командой и последовательностью имён таблиц, а выражение в on () ни на что не влияет

-~{}~ 01.08.06 15:39:

Автор оригинала: 4m@t!c
Эксплейн обоих запросов выложите.

1ый
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE users2 ALL NULL NULL NULL NULL 27290
1 SIMPLE users1 ref login,login_2 login_2 131 func 1

2ой
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE auth_users ALL NULL NULL NULL NULL 49858
1 SIMPLE gerz_users ALL NULL NULL NULL NULL 27290

о чём такая разница говорит? и от чего она?

 

[Clubber]

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

откуда вообще взялось желание джоинить таблицы, используя concat? имхо это еще то извращение.

 

[Лысый]

читаем внимательно - все индексы стоят где надо

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

я просто столкнулся с такой нелогичностью, и решил понять в чём дело

 

[4m@t!c]

Это говорит о количестве перебираемых строк и о результирующей матрице при произведении двух таблиц. В первом запросе кол-во строк в результирующей таблице может быть 27290*1=27290 строк, во втором - 49858*27290=1360624820 строк.
В первом запросе вторая таблица объеденяется с первой используя индексы второй таблицы (users1), а из первой(users2) таблицы просто перебираются все записи.
Во втором запросе при обеъеденении вы не используете индекс, потому что индекс строится по столбцу users2.name, а вы используете concat( 'ge_', users2.name ). Т.е. дла каждой записи из таблицы users1 происходит перебор ВСЕХ записей из таблицы users2 по ПОЛЮ users2.name, а не по его индексу.

Выводы:
1. Учитесь читать EXPLAIN.
2. Меняйте логику приложения.

 

[Лысый]

ага
понял. т.е. при джоине важно наличие индекса только на присоединяемой таблице?

чтож, большое спасибо, теперь всё ясно.

 

[4m@t!c]

это запрос из пакета переноса юзеров с одного движка на другой. иначе никак. выполнается однажды в истории.

Если нужен костыль для эпохального события, то избавтесь от конструкции concat( 'ge_', users2.name ). Для этого достаточно создать еще одно поле, заполнить его значением concat( 'ge_', users2.name ), проиндексировать и далее объеденять запросы по новому столбцу-костылю.

 

[Лысый]

Автор оригинала: 4m@t!c

Выводы:
1. Учитесь читать EXPLAIN.
2. Меняйте логику приложения.

1) ну а где это в EXPLAIN сказано? он просто налл вернул

2) менять слава богу не придётся . обойдусь первым запросом. задача разовая

ещё раз спасибо

 

[Clubber]

читаем внимательно - все индексы стоят где надо

А что это вообще за таблицы такие - auth_users, gerz_users. Так где, ты говоришь, про их индексы написано? Читал внимательно.

Ну ок, продолжим.
Left join точно ограничивает волю mysql optimizer'а на выбор последовательности, в которой джоинить таблицы. Используя left join ты заставляешь mysql джоинить таблицы в порядке auth_users -> gerz_users. И при этом, для каждой записи auth_users ищутся такие значения gerz_users.name, дописав к которым "ge_", получим auth_users.login. Поверь, это не тот случай, с которым могут справиться индексы. А вот если бы в on условии было бы REPLACE(users2.login, "ge_", "") = users1.name, то тут бы индексы наверняка помогли. Хотя решение получилось бы не совсем корректное.

 

[4m@t!c]

1) ну а где это в EXPLAIN сказано? он просто налл вернул

В эксплейне все написано, иначе бы я не просил его. Где сказано? RTFM и все сразу встанет на свои места.

 

[Лысый]

Автор оригинала: Clubber
А что это вообще за таблицы такие - auth_users, gerz_users. Так где, ты говоришь, про их индексы написано? Читал внимательно.

в первом же посте сказано, что логин и нейм - уникальные ключи

про то что с чем джоиниться я хорошо понимаю, но, согласить, от перемены мест операндов в ON() ничего не зависит. паренёк просто не понимает чем лефт(райт) джоины отличаются - думает этим
4m@t!c разъяснил, что раз индекс при конкатенации применить не удаётся - то происходит перебор. как раз об этом я и не подумал, это и был источник тормоза.

кстати простой джоин справился и сам выбрал направление перемножения

 

[Clubber]

Лысый
1)

в первом же посте сказано, что логин и нейм - уникальные ключи

ага, и таблицы там так же назывались? Почему все так и норовят запостить на форуму данные, которые не имеют отношения к реальности? А потом вылазят всякие проблемы.

2)

про то что с чем джоиниться я хорошо понимаю, но, согласить, от перемены мест операндов в ON() ничего не зависит. паренёк просто не понимает чем лефт(райт) джоины отличаются - думает этим

Ну про akxxiv я вообще не упомянал. Вообще, inner join оставляет порядок джоина на усмотрение оптимизатора. Т.е. разрешает начать с конца. С left join это, насколько я понимаю, запроещено.
3)

кстати простой джоин справился и сам выбрал направление перемножения

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