1-n distinct/group by optimization

[elektronaut]

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?

Lars

[Peter]

Any chance you could paste EXPLAIN for this query ?

Generally MySQL would try to optimize Distinct by looking up only one row in the second table if distinct on the first table means only one value from second table will be needed.

[elektronaut]

Sure! thanks for the fast replies!

select distinct(t1.id) ,t1.*from News as t1 inner join News_Channel as t2 on t2.NewsId = t1.id and t2.value in (‘Homepage’,‘Other’)limit 20;1 SIMPLE t2 index value value 773 15816 Using where; Using index; Using temporary1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 languages_pc3_9_0.t2.NewsId 1

performs 1.75 times slower than

select t1.*from News as t1 inner join News_Channel as t2 on t2.NewsId = t1.id and t2.value in (‘Homepage’,‘Other’)limit 20;1 SIMPLE t2 index value value 773 15816 Using where; Using index1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 languages_pc3_9_0.t2.NewsId 1

but of course its not the same thing.