MySQL Performance: MySQL => 1-n distinct/group by optimization

1-n distinct/group by optimization [message #38]

Mon, 14 August 2006 06:25

elektronaut
Messages: 9
Registered: August 2006
Location: Switzerland - Zürich - W...

Junior Member

me again:)

The follwing common problem got me wondering wether there was a more performant solution to this than using either distinct or group by, which is rather hard to decide which one performs better and both might not perform as fast as a different solution might.

select
	distinct(t1.id)
	,t1.*
from
	t1
	inner join t2 on
		t2.pId = t1.id
		and t2.value in ('common','other')
;

what i would wish for is something like this:

select
	t1.*
from
	t1
	inner join t2 LIMIT 1 on
		t2.pId = t1.id
		and t2.value in ('common','other')
;

which would not need any group by or distinct but would ensure that the record of t1 is not included twice, because it matches both.

Does anything like that exist? Any Ideas? Hints? Thoughts? Wink

Lars

[Updated on: Mon, 14 August 2006 06:28]

Minds are like parachutes - they work best when open.

Report message to a moderator