List:General Discussion« Previous MessageNext Message »
From:Fred T. Krogh Date:March 26 1999 8:23pm
Subject:Re: Inverse of ... like "...%"
View as plain text  
Fred T. Krogh wrote:

>> Given a primary key which is CHAR(8) BINARY, I would like to select
all
>> entries which are a leading substring of a given k character string.
>> (Selecting all entries which start with a given substring can be done

>> efficiently with like, and '%', but what I want is apparently a bit
on
>> the weird side.)
>>
>> Thus DKB matches DKB, DK, and D.  I'm guessing the most efficient way
to
>> do this lookup is to simply select on each of these one at a time.
>> ...

Responses, all appreciated, but none of which seem likely to me to be
any more of a solution than I suggested.

Jim Faucette:

>Not necessarily. Consider trying:
>... WHERE
>left(field_name, len_user_supplied_value) = user_supplied_value;
>
>So if they wanted all branches of D they would get it. Or if they only
>wanted branches of DKB, it would be limited to just those.n
>
>This maybe faster than LIKE.

left("D", 3) = "DKB"  would seem to me to give  a "false" instead of the
desired "true".
And besides such a query seems likely to me to look at all entries
instead of just those starting with D.

Rich Teer:
>Try using RLIKE which supports regular expressions...

Once again, this seems likely to me to require looking at all of the
entries rather than just those starting with D.

Sascha Pachev:
>MYSQL has a function substring() . Check the documentation for details.

Same problem as above.

I know you guys know a lot more about databases than I.  But, either I
am very confused or you are overlooking the fact that the indexed look
up only needs to look at the part of the database with entries in this
field starting with D.  Once you bring in a function it seems to me look
ups are going to have look at all the rows.  Is it possible that MySQL
is so clever that it parses the function and uses the index to only look
at a minimal number of rows?  I know it will do this with  ... like
"...%", but would be surprised and extremely impressed if it would do
this with all of the string handling functions.

With the "inverse" of  like, the example of "DKB" could index to the
start of the D entries,  pick up the consectutive D entries, then index
to those starting with DK, pick up the DK entries, and then on to the
DKB entries.  (In writing this I just realized that I said the column in
question was a PRIMARY KEY, but since it contains duplicate entries it
is simply an indexed column.  Perhaps this accounts for some of the
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?

I truly am impressed with how helpful people on this list are.

Regards,
           Fred
fkrogh@stripped


Thread
Inverse of ... like "...%"Fred T. Krogh26 Mar
  • Inverse of ... like "...%"Michael Widenius26 Mar
  • Re: Inverse of ... like "...%"Fred Read26 Mar
Re: Inverse of ... like "...%"Fred T. Krogh27 Mar
Re: Inverse of ... like "...%"Fred Lindberg28 Mar