List:General Discussion« Previous MessageNext Message »
From:Martin Ramsch Date:August 14 1999 3:32pm
Subject:Re: select query ? using like and not like
View as plain text  
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
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