List:General Discussion« Previous MessageNext Message »
From:Jerry Preeper Date:August 14 1999 4:59pm
Subject:Re: select query ? using like and not like
View as plain text  
Martin,

Thanks for the help.  Both work great.  On the limited data I am working
with right now (like 25 records), the REGEXP select ran in 0.00 sec from
the command line, while the FIND_IN_SET select ran in 0.05 sec from the
command line.  I'm running FreeBSD 2.2.8-STABLE with 3.22.14b-gamma.  Once
I get a larger set of data, I will revisit the the speed comparison between
the two.

I really appreciate the response.

Jerry


At 05:32 PM 8/14/99 +0200, Martin Ramsch wrote:
>On Fri, 1999-08-13 22:20:24 -0700,
>Jerry Preeper <preeper@stripped> wrote:
>> Thimble Smith <tim@stripped> writes:
>> > where directory regexp '[[:<:]]dir[[:>:]]'
>[...]
>> That didn't work.  I think it's because my phrases are not considered words
>> since they have a / in them.
>
>  SELECT * FROM users WHERE directory REGEXP '(^| )dir( |$)';
>
>(^| )  at beginning or space before it
>( |$)  space behind it or at end
>
>
>Another possibility is to work with string functions:
>  SELECT * FROM users
>  WHERE FIND_IN_SET('dir', REPLACE(directory, ' ', ','))>0;
>
>Though this looks more complex than the first version with REGEXP,
>my test shows that it's much faster:
>  SELECT BENCHMARK(100000,
>                   FIND_IN_SET('dir', REPLACE(directory, ' ', ','))>0)
>  FROM users;
>  --> 2.33 sec with my test data.
>
>  SELECT BENCHMARK(100000,
>                   directory REGEXP '(^| )dir( |$)')
>  FROM users;
>  --> 28.41 sec with my test data.
>
>So at least for MySQL V3.22.19b on Solaris 2.6 REGEXP seems to be
>terribly slow.
>
>Regards,
>  Martin
>-- 
>Martin Ramsch <m.ramsch@stripped> <URL: http://home.pages.de/~ramsch/
> >
>PGP KeyID=0xE8EF4F75 FiPr=52 44 5E F3 B0 B1 38 26  E4 EC 80 58 7B 31 3A D7
>
>---------------------------------------------------------------------
>Please check "http://www.mysql.com/Manual_chapter/manual_toc.html" before
>posting. To request this thread, e-mail mysql-thread10211@stripped
>
>To unsubscribe, send a message to the address shown in the
>List-Unsubscribe header of this message. If you cannot see it,
>e-mail mysql-unsubscribe@stripped instead.
>
>
>
Thread
select query ? using like and not likeJerry Preeper14 Aug
  • Re: select query ? using like and not likeThimble Smith14 Aug
Re: select query ? using like and not likepreeper@cts.com14 Aug
  • Re: select query ? using like and not likeMartin Ramsch14 Aug
    • Re: select query ? using like and not likeJerry Preeper14 Aug