MySQL query takes too long in codeigniter I've made this query 

SELECT id,name,iupac_name,inchi_key,molecular_weight,molecular_formula,exact_molecular_mass
    FROM compound
    WHERE name LIKE '%quer%'
    OR iupac_name LIKE '%quer%'
    OR inchi_key LIKE '%quer%'
    OR inchi_key IN( SELECT inchi_key FROM compound_synonyms WHERE name LIKE '%quer%')


that took 0.3563 seconds to show result through phpmyadmin in my local machine and took 0.1117 seconds in digital ocean vps.

But the same query took 24+ minutes in another vps.

Does anyone can help me to find out the problem?
Several things make the query slow:


Leading wildcard in LIKE.  (See below)
OR -- One workaround is with UNION.
IN ( SELECT ... ) -- This one is version-dependent; what version is each machine running?


The first two can be alleviated by using a FULLTEXT(name, iupac_name, inchi_key) and MATCH(name, iupac_name, inchi_key) AGAINST('quer*' IN BOOLEAN MODE).