0

I have a companies table that contains several thousands rows and will grow bigger over time. The requirement is to build a search page that will search in title and description fields that are on that table.

I've built a FULLTEXT index and this works well, however I need to be able to search for special characters, for example email addresses if searching for the @ character. Is there a way to achieve this efficiently using MySQL?

s3v3n
  • 8,203
  • 5
  • 42
  • 56
  • Are you using MyISAM or InnoDB storage engine? They are different on how they implement fulltext indexes. – Marki555 May 26 '15 at 17:47
  • I upgraded to `MySQL 5.6` in order to use InnoDB for FULLTEXT indexes, so I don't have to create another MyISAM table for this. If it is required - I can use MyISAM as well. – s3v3n May 26 '15 at 17:58
  • Does this work for you? https://stackoverflow.com/a/8961228/1129642 – Marki555 May 26 '15 at 18:08
  • If not, there is a special way to include additional charaters to be considered part of words in fulltext index. – Marki555 May 26 '15 at 18:09
  • Searching for the entire email address works even without using the quotes, I guess it is interpreted as 2 or 3 words therefore it matches easily. The problem is that we have to search for the "@" itself so I can find companies that listed their email address in the title or description field. – s3v3n May 26 '15 at 18:12
  • IMHO fulltext index is just for searching for words, not arbitrary chars at arbitrary positions... Maybe you will need to use `LIKE` for that. – Marki555 May 26 '15 at 18:15
  • It's totally inefficient to use `LIKE '%@%'` on a table with several thousands of rows :) – s3v3n May 26 '15 at 18:16
  • Or not. It seems to be pretty fast! O_o – s3v3n May 26 '15 at 18:18
  • OK, here's the thing: `LIKE '%@%'` rans pretty fast on my 19K rows (0.09). It takes 2 seconds to run on a table with 2M rows though. I guess I'll use the `LIKE` operator in my particular case, but I'll leave the question open for the 2M rows case. – s3v3n May 26 '15 at 18:33

0 Answers0