0

I have strings like the following in my VARCHAR InnoDB table column:

"This is a {{aaaa->bbb->cccc}} and that is a {{dddd}}!"

Now, I'd like to search for e.g. {{xxx->yyy->zzz}}. Brackets are part of the string. Sometimes searched together with another colum, but which only contains an ordinary id and hence don't need to be considered (I guess).

I know I can use LIKE or REGEXP. But these (already tried) ways are too slow. Can I introduce a fulltext index? Or should I add another helping table? Should I replace the special characters {, }, -, > to get words for the fulltext search? Or what else could I do?

The search works with some ten-thousand rows and I assume that I often get about one hundred hits.

robsch
  • 9,358
  • 9
  • 63
  • 104

1 Answers1

0

This link should give you all the info you need regarding FULLTEXT indexes in MySQL.

MySQL dev site

The section that you will want to pay particular attention to is:

"Full-text searching is performed using MATCH() ... AGAINST syntax. MATCH() takes a comma-separated list that names the columns to be searched. AGAINST takes a string to search for, and an optional modifier that indicates what type of search to perform. The search string must be a string value that is constant during query evaluation. This rules out, for example, a table column because that can differ for each row."

So in short, to answer your question you should see an improvement in query execution times by implementing a full text index on wide VARCHAR columns. Providing you are using a compatible storage engine ( InnoDB or MyISAM)

Also here is an example of how you can query the full text index and also an additional ID field as hinted in your question:

SELECT * 
FROM table 
WHERE MATCH (fieldlist) AGAINST ('search text here') 
    AND ( field2= '1234');
Uberzen1
  • 415
  • 6
  • 18
  • Do I get problems with the mentioned special characters? That's why I think it is not possible this way. – robsch Jan 10 '16 at 19:26
  • You shouldn't do, as long as they are valid VARCHAR characters. The full limitations are available here but in my experience and from reading this page, they shouldn't be an issue http://dev.mysql.com/doc/refman/5.7/en/fulltext-restrictions.html – Uberzen1 Jan 10 '16 at 19:32
  • It doesn't work if there is a value `abc` in the table and I search for `{{abc}}`. This is a false match. The brackets must exist also in the value. – robsch Jan 10 '16 at 19:57
  • I thought that was the result you desired? Can you try searching in binary mode with the search text in full quotes "" ? – Uberzen1 Jan 10 '16 at 19:59
  • Unfortunatly, not. I need the exact match. And boolean mode didn't help either. I think the fts is thought for real words, not for strings with any characters like it is with regexp. – robsch Jan 10 '16 at 20:35
  • Hmmm, that's surprising, and disappointing. It was my understanding that fts should accept a string ,as the index is delimited by spaces between words, so the special chars should not trigger a break in the word? If you are unable to get it to play ball this way then perhaps your other suggestion is the best solution whereby you create a cleansed table with the same data, special chars removed? I am very surprised binary mode did not work for you though? – Uberzen1 Jan 10 '16 at 20:39
  • Not sure how the second table could look like... But you could try it your self: just create a table, add some lines and do a query (Table: ``CREATE TABLE `fts` ( `id` INT(11) NOT NULL AUTO_INCREMENT, `a` VARCHAR(1000) NULL DEFAULT NULL COLLATE 'utf8_unicode_ci', PRIMARY KEY (`id`), FULLTEXT INDEX `a` (`a`) ) COLLATE='utf8_unicode_ci' ENGINE=InnoDB`` ----- Query: `SELECT * FROM fts WHERE MATCH (a) AGAINST ("{{abc}}" IN BOOLEAN MODE) `) – robsch Jan 10 '16 at 20:48
  • Well I'm unsure then. I'm not at a machine with MySQL at the moment but you should be able to use binary mode to search for the exact pattern including special chars, as this is quite a common occurrence as you can imagine – Uberzen1 Jan 10 '16 at 21:11
  • Have a look at the second answer here, see if that solves your problem, as his understanding seems to mirror mine: http://stackoverflow.com/questions/5192499/how-to-allow-fulltext-searching-with-hyphens-in-the-search-query – Uberzen1 Jan 10 '16 at 21:14