18

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?

Community
  • 1
  • 1
Mellon
  • 37,586
  • 78
  • 186
  • 264
  • If you work with large data that needs to be filtered by criteria, then use the right tool for the job, something like Sphinx and then you can stop worrying whether the LIKE, REGEXP or equal is faster. It also depends on your indexes and what not, the topic of what's faster is too broad. Focus on solving the problem, and for that - you require the right tool. – N.B. Nov 16 '11 at 14:45
  • Hi, N.B. What do you mean about it depends on my indexes? – Mellon Nov 16 '11 at 14:46
  • It means that LIKE comparison COULD use an index. If you have a field called `text_data` and an index on that field, you can use `LIKE 'your_seach_string%'` and LIKE *might* use the index in that case. Therefore, it's performance is variable because it might do a full table scan or it might scan the index only. However, it *isn't the right tool for the job* to use LIKE on a large data set. I'll just repeat you're better off reading about Sphinx (it installs as MySQL engine) and using that. – N.B. Nov 16 '11 at 14:48
  • N.B. How about "=" operation, does it always use indexes? – Mellon Nov 16 '11 at 14:57
  • Yep, if one exists. You're not doing partial matching with `=`. – N.B. Nov 16 '11 at 14:59
  • N.B. Yep, I am not doing partial matching, but good to know that = is using index when there is indexes. Thank you. – Mellon Nov 16 '11 at 15:03

2 Answers2

38

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

Johan
  • 74,508
  • 24
  • 191
  • 319
0

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.

ravnur
  • 2,772
  • 19
  • 28