MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:Martin Ramsch Date:September 16 1999 12:55pm
View as plain text  
On Thu, 1999-09-16 02:49:32 +0800, Orlando Andico wrote:
> I am planning to make heavy use of REGEXP in queries. Is this
> efficient?  is the index used in any way? how does it compare,
> performance-wise, to LIKE?

In my experience, REGEXP is very much slower than LIKE.

But the actual speed may depend heavily on the actual regexp library
used on your system, so I suggest that you just do some speed tests
on your own.

There exists the (unfortunately not documented) function BENCHMARK,
that you can use to easily test the calculation speed of expressions
in MySQL.


mysql> SELECT BENCHMARK(1000000, 'SIN(0.5)');
| BENCHMARK(1000000, 'SIN(0.5)') |
|                              0 |
1 row in set (0.41 sec)

So I know, one million calculations of SIN(0.5) take approx. 0.41
seconds on my system at the current work-load.

The first parameter is the number of repetitions, the second parameter
is a string(!) with the SQL expression to test.

Another example:
  If you have a large table with strings to search, you can get good
  speed numbers by:

  SELECT BENCHMARK(10000, ' yourcolumn LIKE "%test%" ') FROM yourtable;
  SELECT BENCHMARK(10000, ' yourcolumn RLIKE "test" ') FROM yourtable;

Martin Ramsch <m.ramsch@stripped> <URL: >
PGP KeyID=0xE8EF4F75 FiPr=52 44 5E F3 B0 B1 38 26  E4 EC 80 58 7B 31 3A D7
REGEXPOrlando Andico15 Sep
  • BENCHMARK (was: REGEXP)Martin Ramsch16 Sep
    • Re: BENCHMARK (was: REGEXP)James Manning16 Sep
      • Re: BENCHMARK (was: REGEXP)Martin Ramsch17 Sep
  • REGEXPMichael Widenius18 Sep