Re: Bit datatype performance?

On Wed, 14 Sep 2011 12:00:35 +0200, Antonio Vieiro wrote:
> Hi all,
>
> One of my entities 'E' may be 'tagged' with an arbitrary set of 256
> tags 'T'.
>
> A first approach could be to add a M:N relationship between 'E' and
> 'T'.
>
> A second way to do this could be to add a BIT(256) datatype to 'E',
> setting bits to '1' if the entity is tagged with each one of the 256
> tags (i.e. using a 'bitmask' on the set of tags).
>
> Since querying entities 'E' with a certain set of tags 'T' must be
> very fast I was wondering if the second approach would be faster.
> What
> do you think?
>
> Thanks for any hints,
> Antonio

I assume each entity may have one or more different tags.

Actually performing test like
... where (t.bits & :mymask) = :mymask
should be quite fast and faster then creating additional relations, but
only if it's highly probable that your query will almost always scan
whole table.

The advantage of indexes is that the index is used 1st and tail
(slower) parts of query will always get "subset" of table. In bitset,
You will probably scan whole table.

So I think, you should do some performance test for large number of
data, and compare both ways. I think bitset will be fast for really
small data, but M:N relations may be faster for really large data sets.

You need to measure size of your database too, in M:N case with 256
tags it may be quite large.