MySQL 2 Million Rows, longtext and varchar indexes I'm trying to find a solution for this, with no success.

I have a table with ~2M rows, and I need to do a WHERE with 3 expressions using OR.

name LIKE '%PARAM%' OR text1 LIKE '%PARAM%' OR text2 LIKE '%PARAM%' ORDER BY views DESC


name is a varchar

text1 and text2 are longtext (~6000 characters each)

text1 and text2 are indexed FULLTEXT
name is indexed BTREE, views too.


The table is MyISAM, it don't have many updates, but many selects;

The query takes ~12sec to complete.

Sorry if this question arleady exists, but I could not found this.

Thank u
Each run of this query will scan the whole table for two reasons:


The index on name will not be used as the value starts with a wild char %
You are not using the proper functions that utilize the fulltext index on the other two fields.


Proposed solution:


add a fulltext index on name field
Use the proper functions to search. in this case, it is match : https://dev.mysql.com/doc/refman/5.5/en/fulltext-search.html#function_match


Notice: Depending on your version of MySQL, using OR to combine conditions could be a performance hit. It is not the case in 5.6 +