Why would a 'fetch' (as in duration/fetch) in MySQL Workbench take a long time for a very limited amount of data?

user_15540596 0
Viewed 561

I am attempting to speed up some sql queries in our system.

One of the problematic queries has a very slow 'IN' clause. I read that I could build a temp table within the query (using UNION ALL) and do a join on that table in order to improve upon the problem caused by implicit 'OR's within an 'IN' clause. (see below)

The old query takes approximately 12 seconds to run on our system (11.8 secs duration / .015 seconds fetch). The new query's duration is ~ 0.15 secs HOWEVER, the new query's fetch is over 11 seconds!) Both queries return the same data. Approximately 236 records each with 30 fields.

Can anyone tell me what is happening here? Why would a fetch take multiple seconds for a few kilobytes of data?

Old Query:

SELECT 
  * 
FROM 
  patients 
  LEFT JOIN patients_billing ON patients.`seq` = patients_billing.`patient_seq` 
WHERE 
  patients.`location` = "XYZ Company" 
  AND patients.`status` = "READY" 
  AND patients.`created` >= "2021-02-15" 
  AND patients.`sub_location` IN(
    'location 1', 
    'location 2', 'location 3', 
    'location 4', 
    'location 5', 
    'location 6', 
    'location 7', 
    'location 8', 
    'location 9', 
    'location 10', 
    'location 11', 
    'location 12', 
    'location 13', 
    'location 14', 
    'location 15', 
    'location 16', 
    ''
  );

New Query:


SELECT 
  * 
FROM 
  patients 
  LEFT JOIN patients_billing ON patients.`seq` = patients_billing.`patient_seq` 
  INNER JOIN (
    SELECT 'location 1' as sub_location
    UNION ALL
    SELECT 'location 2' as sub_location
    UNION ALL
    SELECT 'location 3' as sub_location
    UNION ALL
    SELECT 'location 4' as sub_location
    UNION ALL'
    SELECT 'location 5' as sub_location
    UNION ALL
    SELECT 'location 6' as sub_location
    UNION ALL
    SELECT 'location 7' as sub_location
    UNION ALL
    SELECT 'location 8' as sub_location
    UNION ALL
    SELECT 'location 9' as sub_location
    UNION ALL
    SELECT 'location 10' as sub_location
    UNION ALL
    SELECT 'location 11' as sub_location
    UNION ALL
    SELECT 'location 12' as sub_location
    UNION ALL
    SELECT 'location 13' as sub_location
    UNION ALL
    SELECT 'location 14' as sub_location
    UNION ALL
    SELECT 'location 15' as sub_location
    UNION ALL
    SELECT 'location 16' as sub_location
    UNION ALL
    SELECT '' as sub_location  
  ) as mf_table
    ON mf_table.sub_location = patients.sub_location 
WHERE 
  patients.`location` = "XYZ Company" 
  AND patients.`status` = "READY" 
  AND patients.`created` >= "2021-02-15";

Please feel free to ask any questions for clarification.

Thanks!

mysql-workbench query-optimization mysql
2 Answers

Duration is the time Workbench takes to retrieve the first row of the result set.

Fetch is the time it takes to retrieve the rest of the rows.

It looks like both queries take about the same amount of time to complete. Your UNION ALL formulation of the query delivers the first row quickly, and then trickles out the rest of the rows. Your IN () formulation gets the MySQL server to gather up all the rows and then send them to you at once.

In both cases MySQL is probably repeatedly scanning your entire patients table to find your matches one by one.

I suspect both formulations can be made quite a bit faster with these indexes.

CREATE INDEX patient_billing_x ON patients (status, location, created, seq);
CREATE INDEX billing_patient_x ON patients_billing (patient_seq);

By the way, there's something puzzling about the queries in your question. Your WHERE clauses both mention patients.location in two places that conflict...

WHERE patients.location = 'XYZ Company'
  AND patients.location IN ('this', 'that', 'anything', 'but not XYZ Company')

You should get no rows back with that WHERE clause.

user_2056080 
 WHERE patients.`location` = "XYZ Company" 
  AND patients.`status` = "READY" 
  AND patients.`created` >= "2021-02-15"

Those beg for INDEX(location, status, created), which will speed up the query. (I am assuming that the other test on location was a misspelling.)

user_17668310
Related