List:General Discussion« Previous MessageNext Message »
From:Michael Widenius Date:July 1 1999 10:53pm
Subject:Re: finding distinct SET entries
View as plain text  
>>>>> "Christof" == Christof Damian <cdamian@stripped> writes:

Christof> Michael Widenius wrote:
>> 
>> >>>>> "Christof" == Christof Damian <cdamian@stripped>
> writes:
>> 
Christof> Hello,
Christof> I got a table which contains a set, it looks something like this:
>> 
Christof> col1,col2
Christof> -------------
Christof> aaa,(a,b)
Christof> bbb,(e)
Christof> ccc,(a,d)
Christof> ddd,(a)
>> 
Christof> now I need a select which gives me:
>> 
Christof> a
Christof> b
Christof> d
Christof> e
>> 
Christof> SELECT BIT_OR(col2) gives me the result, but only as binary value and
Christof> there is no way to get back to the set values (or is there?). I also
Christof> have no idea how fast it is with big tables.
>> 
>> Hi!
>> 
>> Try: 
>> 
>> SELECT MAKE_SET(BIT_OR(col2),'a','b','c','d') ...

Christof> Yes, I figured that out myself, but then the application always has to
Christof> know about the database definition. I probably could get that
Christof> information with "SHOW COLUMNS FROM ...", but that still requires some
Christof> string juggling on the application site. Well I use it anyway.

Christof> Which brings me to my second question: Is BIT_OR() optimized to use
Christof> indexes or does it has to look through all rows ?

Hi!

BIT_OR() is a summary function.  This doesn't have to be indexed for
MySQL to be able to use indexes.

Regards,
Monty

PS: If you ask a question about optimization, please ALWAYS include
    some sample query!
Thread
finding distinct SET entriesChristof Damian1 Jul
  • finding distinct SET entriesMichael Widenius1 Jul
  • Re: finding distinct SET entriesChristof Damian2 Jul
    • Re: finding distinct SET entriesMichael Widenius2 Jul
  • Re: finding distinct SET entriesMartin Ramsch2 Jul