REGEXP performance (compare with "LIKE" and "=")

Question

I am using MySQL. I have asked a question about how to query in database for a single word match here.

• There is an answer which suggest me to use REGEXP '[[:<:]]word[[:>:]]'

  It is a good answer, however, I am not sure how is this REGEXP '[[:<:]]word[[:>:]]' thing from performance perspective? If I have a large table, is this way harm the performance of my application?

For example, compare with = operation, e.g. WHERE column_name='value', is the REGEXP operation far more slow than = for large table?

• There is another answer which suggested me to use LIKE, but I think it is not good from performance point of view.

  Then, I googled and found an article which says use LIKE is even faster than REGEXP . I get confused, which way I should use for a single word match query in a large table...

Can I say, = is the fastest operation, then LIKE , and REGEXP is the poorest one from performance perspective?

Answer 1

Regarding regexp

The regexp can never use an index in MySQL.
The = will use an index if:

• an index is declared on the column;
• the values in the column have sufficient cardinality (if more than +/- 20% of the rows match, MySQL will not use an index, because in that case doing a full table scan is faster);
• No other indexes on the same table are better suited (MySQL can only use one index per table per subselect);

Considering these and some other more esoteric caveats an = comparison is much faster than a regexp.

Regarding like

LIKE can use an index if the wildcard is not the first char.

SELECT * FROM t WHERE a LIKE 'abc'   <<-- (case insensitive `=`) can use an index
SELECT * FROM t WHERE a LIKE 'abc%'  <<-- can use an index
SELECT * FROM t WHERE a LIKE 'a%'    <<-- can use an index, depending on cardinality
SELECT * FROM t WHERE a LIKE '%a%'   <<-- cannot use an index
SELECT * FROM t WHERE a LIKE '_agf'  <<-- cannot use an index

The performance of like when using an index is very close to = (assuming the same number of rows returned).

Answer 2

There is another way to search data: Full-Text Search. It can be used when like, = is not enough (executing time) and on the other hand Sphinx, Lucene is too powerfull.

To used it you should create full-text index on a column and query it. If you will use it please be aware of ft_min_word_len, ft_max_word_len system vars that reduce min/max size words.

Hope it helps.

The rest of your question was answered by @Johan.