like效率 regexp_REGEXP性能（与“LIKE”和“=”比较）

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 '[[:<:>:]]'

It is a good answer, however, I am not sure how is this REGEXP '[[:<:>:]]' 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?

解决方案

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

SELECT * FROM t WHERE a LIKE 'abc%' <

SELECT * FROM t WHERE a LIKE 'a%' <

SELECT * FROM t WHERE a LIKE '%a%' <

SELECT * FROM t WHERE a LIKE '_agf' <

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