Запрос из таблиц один ко многим « Базы данных

Сообщения: 292 Репутация: N Группа: Адекваты

21 февраля 2013 г. 20:46

Такая задачка

table 1 (порядка 200к записей)

id name

table2 (соответственно, порядка 1млн записей)

id table1_id field2

Соответственно table1 и table2 соединяются по table1.id и table2.table1_id

Необходимо выбрать записи из первой таблицы по условию

(table2.id = 1 И table2.id = 3)
ИЛИ
(table2.id = 4 И table2.id = 5)
ИЛИ
(table2.id = 7 И table2.id = 9)
….

Помогите оптимизировать запрос :-) Пробовал subquery и join с group by

artoodetoo

Сообщения: 5147 Репутация: N Группа: в ухо

21 февраля 2013 г. 20:57, спустя 11 минут 25 секунд

(table2.id = 1 И table2.id = 3)

может всё-таки "или"?

и вообще непонятно о чём вопрос. приведи текст запроса который собрался оптимизировать.

ιιlllιlllι унц-унц

arvitaly

Сообщения: 292 Репутация: N Группа: Адекваты

21 февраля 2013 г. 21:08, спустя 10 минут 34 секунды

может всё-таки "или"?

и вообще непонятно о чём вопрос. приведи текст запроса который собрался оптимизировать.

Нет И.

Вот например

select * from table1 inner join table2 on (table2.table1_id = table1.id and ( table2.id = 1 or table2.id = 2 ) ) group by table1.id having count(*) = 2

Это для одного условия И, соответственно чтобы объединить их Union?

select * from table1 inner join table2 on (table2.table1_id = table1.id and ( table2.id = 1 or table2.id = 2 ) ) group by table1.id having count(*) = 2
union
select * from table1 inner join table2 on (table2.table1_id = table1.id and ( table2.id = 3 or table2.id = 4 ) ) group by table1.id having count(*) = 2

Пока чот не очень быстро пашет)

Второй вариант еще медленнее, что и логичнее, подзапросы

select * from table1 where

(
select count(*) from table2 where table1.id = table1_id and table2.id = 1)>0
and
select count(*) from table2 where table1.id = table1_id and table2.id = 2)>0
)
or

(
select count(*) from table2 where table1.id = table1_id and table2.id = 3)>0
and
select count(*) from table2 where table1.id = table1_id and table2.id = 4)>0
)

artoodetoo

Сообщения: 5147 Репутация: N Группа: в ухо

21 февраля 2013 г. 22:42, спустя 1 час 33 минуты 58 секунд

Необходимо выбрать записи из первой таблицы по условию
(table2.id = 1 И table2.id = 3) …
может всё-таки "или"?

Нет И.
Вот например
… ( table2.id = 1 or table2.id = 2 ) ) …

таки "ИЛИ" ?

ιιlllιlllι унц-унц

arvitaly

Сообщения: 292 Репутация: N Группа: Адекваты

21 февраля 2013 г. 22:43, спустя 1 минуту 37 секунд

таки "ИЛИ" ?

Чо за троллинг? :-), посмотри запрос внимательнее и поймешь о чем я, а не выдергивай оператор "OR" без контекста)))

artoodetoo

Сообщения: 5147 Репутация: N Группа: в ухо

21 февраля 2013 г. 23:00, спустя 17 минут

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

SELECT table2.id
FROM table1 
INNER JOIN table2 ON (table2.table1_id = table1.id 
WHERE table2.id IN (1,2,3,4)
GROUP BY table1.id 
HAVING count(*) = 2

делать SELECT * при использовании GROUP BY глупо. по смыслу непонятно какие там будут значения. в SELECT должны попадать поля по которым группируеш + агрегатные функции. т.е. вот так логично:
SELECT table2.id, count(*)
или
SELECT table2.id, sum(table1.xxx)
но НЕ вот так. это уже хуйня непредсказуемая:
SELECT table2.id, table1.xxx

такова природа вещей. к сожалению MySQL не пишет ошибку при неправильном использовании GROUP BY, а все другие диалекты SQL ругаются

Спустя 213 сек.

Чо за троллинг? :-), посмотри запрос внимательнее и поймешь о чем я, а не выдергивай оператор "OR" без контекста)))

я процитировал весь твой контекст, умник.
зачем я мечу бисер перед свиньями! иди пасись!

ιιlllιlllι унц-унц

arvitaly

Сообщения: 292 Репутация: N Группа: Адекваты

21 февраля 2013 г. 23:11, спустя 10 минут 26 секунд

я процитировал весь твой контекст, умник.
зачем я мечу бисер перед свиньями! иди пасись!

Иду пасусь, но

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

ты написал то же самое что и я в первом примере это раз.

Мне нужна оптимизация, а не инварианты - это два

AlexB

Сообщения: 4306 Репутация: N Группа: в ухо

22 февраля 2013 г. 0:24, спустя 1 час 13 минут 6 секунд

такова природа вещей. к сожалению MySQL не пишет ошибку при неправильном использовании GROUP BY, а все другие диалекты SQL ругаются

SET sql_mode = 'ONLY_FULL_GROUP_BY'

И о чудо! Мускул тоже начинает вести себя, как взрослые базы данных. )))))

master

Сообщения: 3244 Репутация: N Группа: Джедаи

22 февраля 2013 г. 0:39, спустя 15 минут 12 секунд

про EXPLAIN конечно же забыли

не всё полезно, что в swap полезло

AlexB

Сообщения: 4306 Репутация: N Группа: в ухо

22 февраля 2013 г. 0:43, спустя 4 минуты 28 секунд

про EXPLAIN конечно же забыли

Обычно эту ремарку вопрощающий игнорирует, поэтому в какой-то момент я перестал напоминать …)))))

Nyaah

Сообщения: 574 Репутация: N Группа: Джедаи

22 февраля 2013 г. 0:56, спустя 12 минут 48 секунд

Если я правильно понял задачу, то как-то так:

SELECT
	b.id1,
	b.id2,
	a.*
FROM
	table_1 AS a
	INNER JOIN (
		SELECT
			ba.id        AS id1,
			bb.id        AS id2,
			ba.table1_id AS t1_id
		FROM
			table_2 AS ba INNER JOIN table_2 AS bb ON ba.table1_id=bb.table1_id
		WHERE
			(ba.id=1 AND bb.id=3)
			OR (ba.id=4 AND bb.id=5)
			OR (ba.id=7 AND bb.id=9)
	) b ON a.id=b.t1_id

Чисто теоретически оптимизатор должен выдернуть записи сначала по айди, а потом попытаться сделать объеденение. Если жойн table_2 c table_2 вызывает фулл жойн (не использует индексы), то можно попробовать WHERE перенести в условия жойна, чтобы оптимизатор понял, что сначала нужно по айди выгрести записи и только потом жойнить

SELECT
	b.id1,
	b.id2,
	a.*
FROM
	table_1 AS a
	INNER JOIN (
		SELECT
			ba.id        AS id1,
			bb.id        AS id2,
			ba.table1_id AS t1_id
		FROM
			table_2 AS ba INNER JOIN table_2 AS bb ON (
				ba.table1_id=bb.table1_id
				AND (
					(ba.id=1 AND bb.id=3)
					OR (ba.id=4 AND bb.id=5)
					OR (ba.id=7 AND bb.id=9)
				)
) b ON a.id=b.t1_id

Work, buy, consume, die

arvitaly

Сообщения: 292 Репутация: N Группа: Адекваты

24 февраля 2013 г. 2:36, спустя 2 дня 1 час 39 минут

Если я правильно понял задачу, то как-то так:
SELECT
	b.id1,
	b.id2,
	a.*
FROM
	table_1 AS a
	INNER JOIN (
		SELECT
			ba.id        AS id1,
			bb.id        AS id2,
			ba.table1_id AS t1_id
		FROM
			table_2 AS ba INNER JOIN table_2 AS bb ON ba.table1_id=bb.table1_id
		WHERE
			(ba.id=1 AND bb.id=3)
			OR (ba.id=4 AND bb.id=5)
			OR (ba.id=7 AND bb.id=9)
	) b ON a.id=b.t1_id
Чисто теоретически оптимизатор должен выдернуть записи сначала по айди, а потом попытаться сделать объеденение. Если жойн table_2 c table_2 вызывает фулл жойн (не использует индексы), то можно попробовать WHERE перенести в условия жойна, чтобы оптимизатор понял, что сначала нужно по айди выгрести записи и только потом жойнить
SELECT
	b.id1,
	b.id2,
	a.*
FROM
	table_1 AS a
	INNER JOIN (
		SELECT
			ba.id        AS id1,
			bb.id        AS id2,
			ba.table1_id AS t1_id
		FROM
			table_2 AS ba INNER JOIN table_2 AS bb ON (
				ba.table1_id=bb.table1_id
				AND (
					(ba.id=1 AND bb.id=3)
					OR (ba.id=4 AND bb.id=5)
					OR (ba.id=7 AND bb.id=9)
				)
) b ON a.id=b.t1_id

Да, только не нужно вложенных join, т.е. в итоге

select * from t1
inner join t2 on (t2.t1_id = t1.id and t2.id = 1)
inner join t2 as t3 ( on t3.t1_id = t1.id and t3.id = 2)

UNION

select * from t1
inner join t2 on (t2.t1_id = t1.id and t2.id = 4)
inner join t2 as t3 (on t3.t1_id = t1.id and t3.id = 5)

и так много раз (число сочетаний)
…………

Т.е. избавляемся от subquery и group by в пользу JOIN, UNION у нас играет роль OR.
Если нужен LIMIT - то делаем хранимую функцию и вместо UNION после каждого запроса проверяем число записей, т.е. собственноручно реализуем алгебру.

про EXPLAIN конечно же забыли

Нет, но он нафиг не нужен. Если знаешь как работает оптимизация Mysql и индексы - то он не нужен, если не знаешь - не поможет.

Запрос из таблиц один ко многим

Войти