From: Jay J
Date: April 16 1999 1:15pm
Subject: Most efficient LIKE vs. REGEXP / full-word?
List-Archive: http://lists.mysql.com/mysql/1947
Message-Id: <006a01be880b$2aa692d0$8002a8c0@jay2>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: 7bit

I rely heavily on string-matching, and am curious if someone already* knows
this.

What's the fastest method for matching multiple full-words? Full word
meaning "mysql" preceeded/followed by space/nothing or punctuation.

Also, would that method be the fastest for a single word, or single
sub-word?

Thanks,
-Jay J


I use: MySQL 3.22.21, DBI 1.06, Perl 5.005_02, RH Linux 2.0.36 --
PII-350/128M Ram
----------------------------------------------------------------------
Obviously, these results are very subjective to my particular database, but
they're still interesting..
-- 5 select's per method, sub-word search (i.e. LIKE "%mysql%")
-- Redudant or not, I'm working on a full-word test..... right now.

Average SELECT on database took:         0.560982885714286
Total Run Time:                          19.667646
Queries Per Minute @ avg:          106

Fastest Method [like_count] at [0.4554906]

[like_count]          [ 0.4554906 ]     Q/min [131]
[like]                [ 0.4599836 ]     Q/min [130]
[instr_count]         [ 0.499997 ]      Q/min [120]
[instr]               [ 0.5087934 ]     Q/min [117]
[regexp_count]        [ 0.6021088 ]     Q/min [99]
[regexp]              [ 0.6075416 ]     Q/min [98]
[regexp_lcase]        [ 0.7929652 ]     Q/min [75]