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.
>
>
>
