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).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.