CODEWITHSUNDEEP
mysqlsqlstring
Piter
Piter

Reputation: 61

Mysql search through each word

Let's say I have a column 'x' with a record: 'one two hello five'. I want to search for a specific word (f.e. 'hello') and if it exists - give this record as a result.

  1. I've tried 'SELECT (...) WHERE x LIKE 'hello' - no matches
  2. I dont want to use 'SELECT (...) WHERE x LIKE '%hello%' - cause it will give me also records where 'hello' can be included in another word (f.e. 'rhellog' - which I dont want to)
  3. I dont want regex 'RLIKE '[[:<:]]hello[[:>:]]' - cause it is to slow

Is there any other option, which will be searching for an exact word inside given column, but is not as slow as regex ?

Upvotes: 1

Views: 2609

Answers (6)

Rick James
Rick James

Reputation: 142298

WHERE x LIKE '%hello%'
HAVING x RLIKE '[[:<:]]hello[[:>:]]'

Explanation:

  1. The LIKE is faster, but gets some rows you don't want.
  2. The RLIKE is applied only to the ones filtered by the LIKE, so its sluggishness is not too much of a problem.

(Yes, FULLTEXT would be much faster.)

Upvotes: 0

Bill Karwin
Bill Karwin

Reputation: 562330

You're right, using REGEXP is slow, and so is using LIKE with wildcards.

Your requirements are solved well by a fulltext index. This is a feature built into MySQL: Full-Text Search Functions, or alternatively you can index your data independently using a tool like Apache Solr or Sphinx Search.

You will probably find useful the presentation I made comparing different fulltext search solutions for MySQL.

Upvotes: 0

thisbenroberts
thisbenroberts

Reputation: 21

Have you tried: 

SELECT (...) WHERE x LIKE '% hello %

with spaces on either side of hello?

Upvotes: 0

Amir Raminfar
Amir Raminfar

Reputation: 34149

Usually, with use cases like this, you should think if MySQL is the best database for the job. You can search for %hello% or as others have suggested you can use regex. But you have to realize that these are all going to be slow compared to other databases that actually let you search text.

Your next option is to use match(column) against ('hello') which if you have the latest version can leverage the fulltext search.

Your best option will is to really evaluate your use case. If this is for a small amount of data then any of the above will probably work. But if you have millions of documents and millions of transactions then your best option would be to use something like Elasticsearch where it index words using Lucene.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269763

If you want speed, use the full text search index. If the column contains something like tags separated by spaces, then you have the wrong data structure and need another table.

You can do what you want with like, assuming a consistent word separator:

where concat(' ', x, ' ') like '% hello %'

But this won't be much faster than the regex version. And the regex version is more general because it allows more word separators.

Upvotes: 4

Christian
Christian

Reputation: 827

Try to use

SELECT (...) WHERE x LIKE '% hello %' or x LIKE 'hello %' or x LIKE '% hello'

Upvotes: 0

Related Questions