How to improve slow query performance?

Question

I have a multi-join query that targeting the hospital's chart database.

this takes 5~10 seconds or more.

This is the visual expain using mysql workbench.

The query is below.

select sc.CLIENT_ID as 'guardianId', sp.PET_ID as 'patientId', sp.NAME as 'petName'
, (select BW from syn_vital where HOSPITAL_ID = sp.HOSPITAL_ID and PET_ID = sp.PET_ID order by DATE, TIME desc limit 1) as 'weight'
, sp.BIRTH as 'birth', sp.RFID as 'regNo', sp.BREED as 'vName'
, (case when ss.NAME like '%fel%' or ss.NAME like '%cat%' or ss.NAME like '%pawpaw%' or ss.NAME like '%f' then '002'
when ss.NAME like '%canine%' or ss.NAME like '%dog%' or ss.NAME like '%can%' then '001' else '007' end) as 'sCode'
, (case when LOWER(replace(sp.SEX, ' ', '')) like 'male%' then 'M'
when LOWER(replace(sp.SEX, ' ', '')) like 'female%' or LOWER(replace(sp.SEX, ' ', '')) like 'fam%' or LOWER(replace(sp.SEX, ' ', '')) like 'woman%' then 'F'
when LOWER(replace(sp.SEX, ' ', '')) like 'c.m%' or LOWER(replace(sp.SEX, ' ', '')) like 'castratedmale' or LOWER(replace(sp.SEX, ' ', '')) like 'neutered%' or LOWER(replace(sp.SEX, ' ', '')) like 'neutrality%man%' or LOWER(replace(sp.SEX, ' ', '')) like 'M.N%' then 'MN'
when LOWER(replace(sp.SEX, ' ', '')) like 'woman%' or LOWER(replace(sp.SEX, ' ', '')) like 'f.s%' or LOWER(replace(sp.SEX, ' ', '')) like 'S%' or LOWER(replace(sp.SEX, ' ', '')) like 'neutrality%%' then 'FS' else 'NONE' end) as 'sex'
from syn_client sc
left join syn_tel st on sc.HOSPITAL_ID = st.HOSPITAL_ID and sc.CLIENT_ID = st.CLIENT_ID
inner join syn_pet sp on sc.HOSPITAL_ID = sp.HOSPITAL_ID and sc.FAMILY_ID = sp.FAMILY_ID and sp.STATE = 0
inner join syn_species ss on sp.HOSPITAL_ID = ss.HOSPITAL_ID and sp.SPECIES_ID = ss.SPECIES_ID
WHERE
trim(replace(st.NUMBER, '-','')) = '01099999999'
and trim(sc.NAME) = 'johndoe'
and sp.HOSPITAL_ID = 'HOSPITALID999999'
order by TEL_DEFAULT desc

I would like to know how to improve the performance of this complex query.

Answer 1

The most obvious performance killers in your query are the non-sargable criteria in your where clause.

trim(replace(st.NUMBER, '-','')) = '01099999999'

This cannot use any available index as you have applied a function to the column, which needs to be evaluated before the comparison can be made.

As suggested by Pham, you could change your criterion to -

st.number IN ('01099999999', '01-099-999-999', 'ALL_OTHERS_FORMAT_YOU_ACCEPTS...')

or better still would be to normalize the numbers before you store them (you can always apply formatting for display purposes), that way you know how to search the stored data. Strip all the hyphens and spaces from the existings numbers -

UPDATE syn_tel
SET number = REPLACE(REPLACE(number, '-',''), ' ', '')
WHERE number LIKE '% %' OR number LIKE '%-%';

Similarly for the next criterion -

trim(sc.NAME) = 'johndoe'

The name should be trimmed before being stored in the database so there is no need to trim it when searching it. Update already stored names to trim whitespace -

UPDATE syn_client
SET NAME = TRIM(NAME)
WHERE NAME LIKE ' %' OR NAME LIKE '% ';

Changing sp.HOSPITAL_ID = 'HOSPITALID999999' to sc.HOSPITAL_ID = 'HOSPITALID999999' will allow for the use of a composite index on syn_client (HOSPITAL_ID, name) assuming you drop the TRIM() from the previously discussed criterion.

The sorting in your sub-query for weight might be wrong -

order by DATE, TIME desc limit 1

presumably you want the most recent weight -

order by `DATE` desc, `TIME` desc limit 1
/* OR */
order by CONCAT(`DATE`, ' ', `TIME`) desc limit 1

Answer 2

order by DATE, TIME desc -- really? That's equivalent to date ASC, time DESC. If you want "newest first", then ORDER BY date DESC, time DESC. Furthermore, it is usually bad practice and clumsy to code when you have DATE and TIME in separate columns. Is there a strong reason for storing them separately? It is reasonably easy to split them apart in a SELECT.

Similarly, cleanse NUMBER and NAME when inserting.

This will make the first subquery much faster:

syn_vital needs INDEX(hostital_id, pet_id, date, time, BW)

LIKE with a leading wildcard (%) is slow, but you probably cannot avoid it in this case.

LOWER(replace(sp.SEX, ' ', '')) -- Cleanse the input during INSERT, not on output!.

LOWER(...) -- With a suitable COLLATION (eg, the default), calling LOWER is unnecessary.

Some of these 'composite' INDEXes may be useful:

ss:  INDEX(HOSPITAL_ID, SPECIES_ID, NAME)
st:  INDEX(HOSPITAL_ID, CLIENT_ID, NUMBER)
sp:  INDEX(HOSPITAL_ID, PET_ID)

What table is TEL_DEFAULT in?

Answer 3

You may want to:

1. Create index on syn_client(hospital_id, name --,tel_default?)

2. Create index on syn_tel(hospital_id, client_id, number)

3. Create index on syn_pet(hospital_id, family_id, state)

4. Create index on syn_species(hospital_id, species_id)

5. Change your query to:

    SELECT ...
    FROM syn_client sc
    INNER JOIN syn_tel st ON sc.hospital_id = st.hospital_id AND sc.client_id = st.client_id
    INNER JOIN syn_pet sp ON sc.hospital_id = sp.hospital_id AND sc.family_id = sp.family_id AND sp.state = 0
    INNER JOIN syn_species ss ON sp.hospital_id = ss.hospital_id AND sp.species_id = ss.species_id
    WHERE st.number IN ('01099999999', '01-099-999-999', 'ALL_OTHERS_FORMAT_YOU_ACCEPTS...')
        AND trim(sc.name) = 'johndoe'   --sc.name = 'johndoe' with standardize data input
        AND sc.hospital_id = 'HOSPITALID999999' --not sp.hospital_id
    ORDER BY tel_default DESC;