List:General Discussion« Previous MessageNext Message »
From:Michael Dykman Date:November 22 2012 3:25pm
Subject:Re: Basic SELECT help
View as plain text  
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.tompkins@stripped> 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 <mdykman@stripped> 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.tompkins@stripped>
>> 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 <mdykman@stripped> wrote:
>>>
>>> >
>>> > response did not go to the list..
>>> >
>>> >
>>> > I assume that you mean the id must be associated with bo...
>>>
>>> >
>>> >
>>> > --
>>> >  - michael dykman
>>> >  - mdykman@stripped
>>> >
>>> >  May the Source be with you.
>>> >
>>>
>>> > --
>>> > MySQL General Mailing List
>>> > For list archives: http://lists.mysql.com/mysql
>>> > To unsubscribe...
>>
>>
>



-- 
 - michael dykman
 - mdykman@stripped

 May the Source be with you.
Thread
Fwd: Basic SELECT helpMichael Dykman22 Nov
  • Re: Basic SELECT helpNeil Tompkins22 Nov
    • Re: Basic SELECT helpMichael Dykman22 Nov
      • Re: Basic SELECT helpNeil Tompkins22 Nov
        • Re: Basic SELECT helpMichael Dykman22 Nov
    • Re: Basic SELECT helpBen Mildren22 Nov
      • Re: Basic SELECT helpBen Mildren22 Nov
        • Re: Basic SELECT helpBen Mildren22 Nov
      • Re: Basic SELECT helpClaudio Nanni23 Nov
        • Re: Basic SELECT helpNeil Tompkins23 Nov
Re: Basic SELECT helpMogens Melander23 Nov
  • Re: Basic SELECT helpdivesh kamra23 Nov