CODEWITHSUNDEEP
mysqlsqlsearch
david
david

Reputation: 3360

How to search for a text? (MySQL)

I have this table:

bussId    |      nameEn        |          keywords
  500          name1 name2             keyword1 keyword2

I want to return bussId 5000 if the user search for (keyword1 or keyword2 or name2 or name1). So I should use this query SELECT * FROM business WHERE nameEn LIKE '%searched_word%'. But this query doesn't use the index nameEn or keywords, according to Comparison of B-Tree and Hash Indexes "The index also can be used for LIKE comparisons if the argument to LIKE is a constant string that does not start with a wildcard character".

I have this solution, I want to create another table and insert all the single words:

bussId |  word
  500        name1
  500        name2
  500        keyword1
  500        keyword2

Then I will search for the bussId using this query:
SELECT * WHERE word LIKE 'searched_word%'.

In that way I will be sure that the MySQL will use the index , and it will be faster, but this table will contain about 20 million rows!

Is there another solution?

Upvotes: 9

Views: 5970

Answers (5)

Engin
Engin

Reputation: 414

20 million records is quite a lot and a mapping table with varchar column would allocate the max allowed chars in byte for each row + 32-bit for integer column.

What if you could just create a table like (id int, crc int) and store only the text data's crc32 value. It's case sensitive so you need to convert to uppercase/lowercase while populating the data and the same when comparing.

I agree with the full-text approach but to save space and use the advantage of indexing, you can try something like below.

Create Temporary TABLE t (id INT, crc INT);

Insert Into t
Select 500, CRC32(UPPER('name1'))
Union Select 500, CRC32(UPPER('name2'))
Union Select 500, CRC32(UPPER('keyword1'))
Union Select 500, CRC32(UPPER('keyword2'));

Select * From t Where crc = CRC32(UPPER('keyword2');

Upvotes: 0

Ritobroto Mukherjee
Ritobroto Mukherjee

Reputation: 111

Use the below statement in MySQL or SQL it'll return perfect result: 

SELECT * FROM business WHERE (nameEn LIKE 'searched_word%' OR nameEn LIKE '%searched_word%') OR (keywords LIKE 'searched_word%' OR keywords LIKE '%searched_word%') AND bussID = 500;

This should work.

Upvotes: 0

Adam
Adam

Reputation: 18865

You have to use a fulltext index using MyISAM or InnoDB from MySQL 5.6 onwards:

mysql> ALTER TABLE business ADD FULLTEXT(nameEn, keywords);

And here is your request:

mysql> SELECT * FROM business
   -> WHERE MATCH (nameEn, keywords) AGAINST ('searched_word');

Upvotes: 8

Gevorg M
Gevorg M

Reputation: 51

With new version of MySQL you don't need to make engine "MyISAM", InnoDB is also support FULLTEXT index (I've tested this 5.6.15, supports form version >=5.6.4 ). So if your server version higher then 5.6.4 than you need just add FULLTEXT index to your table and make select with MATCH(...)AGAINST(...), example below 

CREATE FULLTEXT INDEX idx ON business (nameEn);

SELECT * FROM business 
WHERE match(nameEn)against('+searched_word' IN BOOLEAN MODE);

Upvotes: 0

Parfait
Parfait

Reputation: 107767

Did you try the Instr() or Locate() functions? Here is a SO discussion comparing them with Like but may prove better comparing a front % wildcard. Still it runs full table scans but unaware how the MySQL query optimizer indexes with string functions.

SELECT * FROM business WHERE Instr(nameEN, 'search_word') > 0

OR 

SELECT * FROM business WHERE Locate(nameEN, 'search_word') > 0

Also, there may be other areas of optimization. See if other potential indices are available in the business table, explicitly declare specific columns instead of the asterisk (*) if all columns are not being used, and parse the nameEN and keywords columns by the spaces so columns retain one value (with potential to transpose), then use the implicit join, WHERE, or explicit join, JOIN. This might even be a table design issue with the challenge of storing multiple values in a singe field.

Upvotes: 1

Related Questions