From: Fred Lindberg
Date: March 26 1999 10:48pm
Subject: Re: Inverse of ... like "...%"
List-Archive: http://lists.mysql.com/mysql/1075
Message-Id: <19990326225016.30832.qmail@id.wustl.edu>
MIME-Version: 1.0
Content-Type: text/plain; charset="iso-8859-1"
Content-Transfer-Encoding: 7bit

On Fri, 26 Mar 1999 20:23:04 +0000, Fred T. Krogh wrote:

>responses?)  The approach I mention above, i.e. looking for D entries,
>then DK entries and then DKB entries will take advantage of the indexing
>that is available, and thus should be reasonable fast?

Yes. Hard to think of a faster way, especially since it will only be at
most 3 "sets". Combined in one query you'd also eliminate duplicates,
i.e. WHERE x='D' OR x='DK' OR x='DKB'.

An alternative would be to limit the mail categories to e.g. 32 and use
5 bits, limit the second set to 5 bits and the third level to 6 bits.
In each case, the value 0 mens no restriction. You or course need to
translate categories to a number, rather than a letter (you need to
encode the categories anyway (i.e. it doesn't matter if crypto => 'C'
or crypto => 10).

Thus, if D1 = 10, K2= 3, and B(level 3)=2, you'd have 10,3,2 and you'd
look for x=(10 << 11) OR x=(10 << 5 + 3 )<< 6 OR x=(10<<5 + 3) <<6 + 2.

The time taken for those computations is negligable, but all your
records would be indexed on a single 32-bit integer rather than a
CHAR(8). To me this is simple enough that I'd always do it, YMMV, and
access may be so fast that the gain is negilgable as well.


-Sincerely, Fred

(Frederik Lindberg, Infectious Diseases, WashU, St. Louis, MO, USA)