Of course there is a cost for the join, each link being a distinct lookup query but that is the same cost the INTERSECT would impose.
It is not a bad as multiple joins generally might be as all the lookups are against the same key in the same table which should keep that index in ram. (type is indexed, yes?) As you no doubt have noticed, the problem with these solutions: SELECT id FROM mytable WHERE type IN(x,y,z) GROUP BY id; is that they returns ids that have ANY of those values which is not what you are asking for, If your dataset is HUGE, there might be a performance problem which might force you to reformulate as: create temporary table `mytemp` select id, type from `mytable` WHERE type IN(x,y,z); select distinct a.id from `mytemp` a inner join `mytemp` b on (a.id=b.id) where a.type= 2 and b.type = 5; -- repeat inner join as needed drop table mytemp; On Thu, Nov 22, 2012 at 10:09 AM, Neil Tompkins <neil.tompk...@googlemail.com> wrote: > > Do you know if I had multiple joins there would be a performance issue ? > > > On Thu, Nov 22, 2012 at 3:06 PM, Michael Dykman <mdyk...@gmail.com> wrote: >> >> Keep joining I think. In the absence of intersect (which incurs the cost >> of a query per type anyhow ), this join pattern is the only option I can >> think of. >> >> On 2012-11-22 10:01 AM, "Neil Tompkins" <neil.tompk...@googlemail.com> >> wrote: >> >> Michael, >> >> Thanks this kind of works if I'm checking two types. But what about if I >> have 5 types ? >> >> On Thu, Nov 22, 2012 at 2:53 PM, Michael Dykman <mdyk...@gmail.com> wrote: >>> >>> > >>> > response did not go to the list.. >>> > >>> > >>> > I assume that you mean the id must be associated with bo... >>> >>> > >>> > >>> > -- >>> > - michael dykman >>> > - mdyk...@gmail.com >>> > >>> > May the Source be with you. >>> > >>> >>> > -- >>> > MySQL General Mailing List >>> > For list archives: http://lists.mysql.com/mysql >>> > To unsubscribe... >> >> > -- - michael dykman - mdyk...@gmail.com May the Source be with you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql
