How can I speed up my query. subquery is too slow

Question

The query I have is for a table of inventory. What the subquery join does is gets the total number of work orders there are for each inventory asset. If I run the base query with the main joins for equipment type, vendor, location and room, it runs just fine. Less than a second to return a result. using it with the subquery join, it takes 15 to 20 seconds to return a result.

Here is the full query:

SELECT `inventory`.inventory_id AS 'inventory_id', 
       `inventory`.media_tag AS 'media_tag', 
       `inventory`.asset_tag AS 'asset_tag', 
       `inventory`.idea_tag AS 'idea_tag', 
       `equipTypes`.equipment_type AS 'equipment_type',  
       `inventory`.equip_make AS 'equip_make', 
       `inventory`.equip_model AS 'equip_model', 
       `inventory`.equip_serial AS 'equip_serial', 
       `inventory`.sales_order AS 'sales_order', 
       `vendors`.vendor_name AS 'vendor_name', 
       `inventory`.purchase_order AS 'purchase_order', 
       `status`.status AS 'status', 
       `locations`.location_name AS 'location_name', 
       `rooms`.room_number AS 'room_number', 
       `inventory`.notes AS 'notes', 
       `inventory`.send_to AS 'send_to', 
       `inventory`.one_to_one AS 'one_to_one', 
       `enteredBy`.user_name AS 'user_name', 
       from_unixtime(`inventory`.enter_date, '%m/%d/%Y') AS 'enter_date', 
       from_unixtime(`inventory`.modified_date, '%m/%d/%Y') AS 'modified_date', 
       COALESCE(at.assets,0) AS assets 
FROM mod_inventory_data AS `inventory` 
LEFT JOIN mod_inventory_equip_types AS `equipTypes` 
       ON `equipTypes`.equip_type_id = `inventory`.equip_type_id 
LEFT JOIN mod_vendors_main AS `vendors`  
       ON `vendors`.vendor_id = `inventory`.vendor_id 
LEFT JOIN mod_inventory_status AS `status`  
       ON `status`.status_id = `inventory`.status_id 
LEFT JOIN mod_locations_data AS `locations`  
       ON `locations`.location_id = `inventory`.location_id 
LEFT JOIN mod_locations_rooms AS `rooms`  
       ON `rooms`.room_id = `inventory`.room_id 
LEFT JOIN mod_users_data AS `enteredBy`  
       ON `enteredBy`.user_id = `inventory`.entered_by
LEFT JOIN  
       ( SELECT asset_tag, count(*) AS assets 
         FROM mod_workorder_data 
         WHERE asset_tag IS NOT NULL 
         GROUP BY asset_tag ) AS at  
       ON at.asset_tag = inventory.asset_tag 
ORDER BY inventory_id ASC LIMIT 0,20

The MySQL EXPLAIN data for this is here

+----+-------------+--------------------+--------+---------------+-----------+---------+-------------------------------------+-------+---------------------------------+
| id | select_type | table              | type   | possible_keys | key       | key_len | ref                                 | rows  | Extra                           |
+----+-------------+--------------------+--------+---------------+-----------+---------+-------------------------------------+-------+---------------------------------+
|  1 | PRIMARY     | inventory          | ALL    | NULL          | NULL      | NULL    | NULL                                | 12612 | Using temporary; Using filesort |
|  1 | PRIMARY     | equipTypes         | eq_ref | PRIMARY       | PRIMARY   | 4       | spsd_woidbs.inventory.equip_type_id |     1 |                                 |
|  1 | PRIMARY     | vendors            | eq_ref | PRIMARY       | PRIMARY   | 4       | spsd_woidbs.inventory.vendor_id     |     1 |                                 |
|  1 | PRIMARY     | status             | eq_ref | PRIMARY       | PRIMARY   | 4       | spsd_woidbs.inventory.status_id     |     1 |                                 |
|  1 | PRIMARY     | locations          | eq_ref | PRIMARY       | PRIMARY   | 4       | spsd_woidbs.inventory.location_id   |     1 |                                 |
|  1 | PRIMARY     | rooms              | eq_ref | PRIMARY       | PRIMARY   | 4       | spsd_woidbs.inventory.room_id       |     1 |                                 |
|  1 | PRIMARY     | enteredBy          | eq_ref | PRIMARY       | PRIMARY   | 4       | spsd_woidbs.inventory.entered_by    |     1 |                                 |
|  1 | PRIMARY     | <derived2>         | ALL    | NULL          | NULL      | NULL    | NULL                                |  4480 |                                 |
|  2 | DERIVED     | mod_workorder_data | range  | asset_tag     | asset_tag | 13      | NULL                                | 15897 | Using where; Using index        |
+----+-------------+--------------------+--------+---------------+-----------+---------+-------------------------------------+-------+---------------------------------+

Using MySql query profiling I get this:

+--------------------------------+------------+
| Status                         | Time       |
+--------------------------------+------------+
| starting                       |  0.000020  | 
| checking query cache for query |  0.000263  |
| Opening tables                 |  0.000034  |
| System lock                    |  0.000013  |
| Table lock                     |  0.000079  |
| optimizing                     |  0.000011  |
| statistics                     |  0.000138  |
| preparing                      |  0.000019  |
| executing                      |  0.000010  |
| Sorting result                 |  0.000004  |
| Sending data                   |  0.015103  |
| init                           |  0.000094  |
| optimizing                     |  0.000009  |
| statistics                     |  0.000049  |
| preparing                      |  0.000022  |
| Creating tmp table             |  0.000104  |
| executing                      |  0.000009  |
| Copying to tmp table           | 15.410168  |
| Sorting result                 |  0.009488  |
| Sending data                   |  0.000215  |
| end                            |  0.000006  |
| removing tmp table             |  0.001997  |
| end                            |  0.000018  |
| query end                      |  0.000005  |
| freeing items                  |  0.000112  |
| storing result in query cache  |  0.000011  |
| removing tmp table             |  0.000022  |
| closing tables                 |  0.000036  |
| logging slow query             |  0.000005  |
| logging slow query             |  0.000005  |
| cleaning up                    |  0.000013  |
+--------------------------------+------------+

which shows me that the bottle neck is copying to temp table, but I am unsure of how to speed this up. Are there settings on the server end that I can configure to make this faster? Are there changes to the existing query that I can do that will yield the same results that would be faster?

It seems to me that the LEFT JOIN subquery would give the same resulting data matrix every time, so if it has to run that query for every row in the inventory list, I can see why it would be slow. Or does MySQL cache the subquery when it runs? I thought I read somwhere that MySQL does not cache subqueries, is this true?

Any help is appreciated.

Answer 1

Here is what I did which seems to be working good. I created a table called mod_workorder_counts. The table has two fields, Asset tag which is unique, and wo_count which is and INT(3) field. I am populating that table with this query:

INSERT INTO mod_workorder_counts ( asset_tag, wo_count ) 
select s.asset_tag, ct 
FROM
  ( SELECT t.asset_tag, count(*) as ct
    FROM mod_workorder_data t
    WHERE t.asset_tag IS NOT NULL
    GROUP BY t.asset_tag
  ) as s
ON DUPLICATE KEY UPDATE mod_workorder_counts.wo_count = ct

which executed in 0.1580 seconds which may be considered slightly slow, but not bad.

Now when I run this modification of my original query:

SELECT `inventory`.inventory_id AS 'inventory_id', 
       `inventory`.media_tag AS 'media_tag', 
       `inventory`.asset_tag AS 'asset_tag', 
       `inventory`.idea_tag AS 'idea_tag', 
       `equipTypes`.equipment_type AS 'equipment_type',  
       `inventory`.equip_make AS 'equip_make', 
       `inventory`.equip_model AS 'equip_model', 
       `inventory`.equip_serial AS 'equip_serial', 
       `inventory`.sales_order AS 'sales_order', 
       `vendors`.vendor_name AS 'vendor_name', 
       `inventory`.purchase_order AS 'purchase_order', 
       `status`.status AS 'status', 
       `locations`.location_name AS 'location_name', 
       `rooms`.room_number AS 'room_number', 
       `inventory`.notes AS 'notes', 
       `inventory`.send_to AS 'send_to', 
       `inventory`.one_to_one AS 'one_to_one', 
       `enteredBy`.user_name AS 'user_name', 
       from_unixtime(`inventory`.enter_date, '%m/%d/%Y') AS 'enter_date', 
       from_unixtime(`inventory`.modified_date, '%m/%d/%Y') AS 'modified_date', 
       COALESCE(at.wo_count, 0) AS workorders 
FROM mod_inventory_data AS `inventory` 
LEFT JOIN mod_inventory_equip_types AS `equipTypes` 
       ON `equipTypes`.equip_type_id = `inventory`.equip_type_id 
LEFT JOIN mod_vendors_main AS `vendors`  
       ON `vendors`.vendor_id = `inventory`.vendor_id 
LEFT JOIN mod_inventory_status AS `status`  
       ON `status`.status_id = `inventory`.status_id 
LEFT JOIN mod_locations_data AS `locations`  
       ON `locations`.location_id = `inventory`.location_id 
LEFT JOIN mod_locations_rooms AS `rooms`  
       ON `rooms`.room_id = `inventory`.room_id 
LEFT JOIN mod_users_data AS `enteredBy`  
       ON `enteredBy`.user_id = `inventory`.entered_by
LEFT JOIN mod_workorder_counts AS at  
       ON at.asset_tag = inventory.asset_tag 
ORDER BY inventory_id ASC LIMIT 0,20

It executes in 0.0051 seconds. That puts a total between the two queries at 0.1631 seconds which is near 1/10th of a second versus 15+ seconds with the original subquery.

If I just included the field "wo_count" without using the COALESCE, I got NULL values for any asset tags that were not listed in the "mod_workorder_counts" table. So the COALESCE would give me a 0 for any NULL value, which is what I want.

Now I will set it up so that when a work order is entered for an asset tag, i'll have the INSERT/UPDATE query for the counts table update at that time so it doesn't run unnecessarily.