MySQL query takes too long in codeigniter
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).