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

Members

Help

Home

Home » Performance » MySQL » 1-n distinct/group by optimization

Show: Today's Messages :: Show Polls :: Message Navigator

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

Re: 1-n distinct/group by optimization [message #39 is a reply to message #38 ]

Mon, 14 August 2006 06:51

Peter
Messages: 405
Registered: August 2006

Senior Member
Super Guru

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.

Peter Zaitsev, MySQL Performance Expert
MySQL Performance Blog - http://www.mysqlperformanceblog.com
MySQL Consulting http://www.mysqlperformanceblog.com/mysql-consulting/

Report message to a moderator

Re: 1-n distinct/group by optimization [message #40 is a reply to message #39 ]

Mon, 14 August 2006 07:50

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

Junior Member

Sure! thanks for the fast replies! Wink

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 temporary
1	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 index
1	SIMPLE	t1	eq_ref	PRIMARY	PRIMARY	4	languages_pc3_9_0.t2.NewsId	1

but of course its not the same thing.

Minds are like parachutes - they work best when open.

Report message to a moderator

Re: 1-n distinct/group by optimization [message #41 is a reply to message #40 ]

Mon, 14 August 2006 15:09

Peter
Messages: 405
Registered: August 2006

Senior Member
Super Guru

Sure it does. In this case there is no "distict" optimization applied as per explain.

On the contrary - temporary table needs to be created to eliminate dupes.

Note also DISTINCT is not a function but rathe keyword which means duplicate rows are to be removed. Do not be mistaken thinking there would be only different values t1.id in the result set.

Function like syntax is accepted but it is only missleading.

What you're looking for can be done by using something like:

select t1.id, (select t2.f_id from t1 t2 where t2.id=t1.f_id limit 1) from t1;

Peter Zaitsev, MySQL Performance Expert
MySQL Performance Blog - http://www.mysqlperformanceblog.com
MySQL Consulting http://www.mysqlperformanceblog.com/mysql-consulting/

Report message to a moderator

Re: 1-n distinct/group by optimization [message #46 is a reply to message #41 ]

Tue, 15 August 2006 03:34

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

Junior Member

Thanks for the explanation! I guess I just expected a little too much;)

Minds are like parachutes - they work best when open.

Report message to a moderator

Previous Topic:	Using SQL_CALC_FOUND_ROWS
Next Topic:	MySQL Profiler

Goto Forum:

-=] Back to Top [=-

[ Generate printable PDF ] [ Syndicate this forum (XML) ] [

]

Current Time: Fri Dec 5 11:17:39 EST 2008

Total time taken to generate the page: 0.00510 seconds

.:: Contact :: Home :: MySQL Support by Percona.com ::.