MySql `MATCH AGAINST` and `LIKE` combination to search for special characters

Question

For example, in database i have row with phrase DN-NP. In input field i type DN- and want to find the row. Here is example http://sqlfiddle.com/#!9/f9235a/4 Tried to use FULLTEXT index and MATCH AGAINST

SELECT `name` 
FROM `domains` 
WHERE MATCH (`name`) AGAINST ('DN*' IN BOOLEAN MODE);

get no results.

Here https://dba.stackexchange.com/a/111895/55397 is advice to combine with LIKE. At the moment idea is in php something like

if( strlen($_POST['input_field_value']) <= 2 ){ 
$sql = 'SELECT `name` 
FROM `domains` 
WHERE MATCH (`name`) AGAINST ('DN*' IN BOOLEAN MODE)
OR `name` LIKE "%DN%"'; 
}

But LIKE "% %" too slow? Any ideas how to solve the problem (to find phrases that contains special characters)?

What about LOCATE (performance)?

SELECT name AS name7 
FROM domains 
WHERE LOCATE('DN',`name`)>0;

Answer 1

Indexes can help with speed by limiting the number of rows to look at. Most code shown so far requires testing every row.

• FULLTEXT is very good at finding rows when its rules apply. I doubt if +DN* applies due to word-length and existence of punctuation.
• `LIKE "DN-NP%" can use an index very efficiently. But that only works for the string being at the start of the column.
• `LIKE "%DN-NP%" -- The leading wildcard requires checking every row.
• LOCATE and any other string operator -- not sargable, so needs to look at every row.
• REGEXP "DN-NP" -- slower than LIKE. (There are other situations where REGEXPcan be faster and/orLIKE` won't apply.)

If you have the min word-length set to 2, then this trick may be the most efficient:

WHERE MATCH(col) AGAINST("+DN +NP" IN BOOLEAN MODE)
  AND col LIKE '%DN-NP%'

The MATCH will efficiently whittle down the number of rows; the LIKE will make further whittle down the number or rows, but only looking at the small number from the MATCH.

Caveat: Which of these do you need to match or not match?:

abc DN-NP def
abc DNs-NPed def   -- look likes "plural", etc which FULLTEXT matches
abc DN-NPQRS def   -- word boundary issue
abc ZYXDN-NP def

REGEXP can match a "word boundary"; LIKE does not have such.

Please build a list of things you want to match / not match. We might have a better answer for you.

Answer 2

how about regexp ?

SELECT name AS name6 FROM domains WHERE name regexp ('DN');