Why is fulltext index search slower than like in mysql?

Question

I am using mysql.

I use a fulltext index for searching, I know this is faster than a like search.

But in my actual query, fulltext index search is much slower than like.

#1
SELECT ...
FROM CAT
INNER JOIN DOCTOR ON CAT.DOCTORID = DOCTOR.ID
WHERE ( CAT.NAME1 LIKE '%{searchKeyword}%'
  OR CAT.NAME2 LIKE '%{searchKeyword}%'
  OR CAT.NAME3 LIKE '%{searchKeyword}%'
  OR DOCTOR.NAME LIKE '%{searchKeyword}%'
)

#2 
SELECT ...
FROM CAT
INNER JOIN DOCTOR ON CAT.DOCTORID = DOCTOR.ID
WHERE MATCH(CAT.NAME1) AGAINST('+"{searchKeyword}"' IN BOOLEAN MODE)
  OR MATCH(CAT.NAME2) AGAINST('+"{searchKeyword}"' IN BOOLEAN MODE)
  OR MATCH(CAT.NAME3) AGAINST('+"{searchKeyword}"' IN BOOLEAN MODE)
  OR MATCH(DOCTOR.NAME) AGAINST('+"{searchKeyword}"' IN BOOLEAN MODE)
)

If I change '+"{searchKeyword}"' to '{searchKeyword}' in #2, it takes the same time.

I know fulltext index is faster than like, am I wrong?

The index was created separately for cat.name1,2,3 using ngram parser. And n=2

Time required when using like : 0.067s Time required when using match-against : 2.67s

The data is about 400,000 cases.

Answer 1

OR kills performance. Almost always it disables any use of INDEXes of any kind.

What to do...

Have an extra column that (redundantly) contains all the search words in it. Apply a FULLTEXT index on it. No ORs.

With that, FULLTEXT will be faster and will scale much better than LIKE with a leading wildcard.

(Akina's UNION DISTINCT is slower than a combined FULLTEXT; it is useful for messier situations than what you have.)

Revised

Your query is confusing... You want to find all

1. All cats with a given name, if any, plus the doctor(s) that treated those cats, and/or
2. All doctors with a given name, if any, plus all the cats they treated?

First, do those as two separate queries. Better yet, force the user to provide either a cat name or a doctor name. Those are too dissimilar to try to mix them.

Then the CAT query is something like

SELECT ...
    FROM CAT
    WHERE MATCH(name1, name2, name3)
          AGAINST('+"{searchKeyword}"' IN BOOLEAN MODE)

Together with

FULLTEXT(name1, name2, name3)

That Select could be JOINed to DOCTOR, if desired.

Answer 2

You can construct the required 3 columns as follows.

val df = df1.join(df2, Seq("col1"), "left").select(coalesce(df2("col3"), df1("col1")).as("col1"),col("col2"), col("colx"))

Answer 3

For get all columns from "df1" after join, alias can be used for Dataframe:

val updatedCol1 = coalesce(df2("col3"), df1("col1")).alias("col1")
val columns = updatedCol1 :: df1.columns
  .filterNot(_ == "col1")
  .map(cname => col("df1." + cname))
  .toList

df1.alias("df1")
  .join(df2, Seq("col1"), "left")
  .select(columns: _*)