MySQL: improving efficiency of sub-select query

Question

I have a set of (MySQL) tables as shown below. I need to retrieve a list of Trouble Tickets, as well as the id of matching equipment in either of the equipment tables, based on matching model/serial numbers.

Note that model/serial number combinations should be unique across both equipment tables, but the model/serial number entry boxes are free-form, so there's the possibility the user could enter the same model/serial twice. In such case, it doesn't matter which equipment unit is retrieved, but only result should be returned per ticket, since we're displaying a list of tickets, not equipment.

'tickets' [Trouble Tickets table]

id [index]
model [Equipment Model Number]
serial [Equipment Serial Number]
... etc

user_equipment [User Equipment table]

id [index]
model [Equipment Model Number]
serial [Equipment Serial Number]
... etc

site_equipment [Onsite Equipment]

id [index]
model [Equipment Model Number]
serial [Equipment Serial Number]
... etc

Currently I'm using sub-queries to return the user and site equipment IDs, but performance is very poor:

SELECT tickets.*, ... other tables/columns here,
   (SELECT id FROM user_equipment WHERE model = tickets.model AND serial = tickets.serial LIMIT 1) as user_equipment_id,
   (SELECT id FROM site_equipment WHERE model = tickets.model AND serial = site_equipment.serial LIMIT 1) as site_equipment_id
FROM
  tickets
  ... other joins here
WHERE ... 
HAVING ... 
ORDER BY ...
LIMIT ...

I would greatly appreciate any suggestions for improving the performance of this query. Changing the table structure is, unfortunately, not an option at this point, due to many other dependencies.

Thanks!

Rick James · Accepted Answer · 2017-11-26 02:45:36Z

2

You want indexes on:

user_equipment(model, serial, id)
site_equipment(model, serial, id)

The first two columns can be in either order.

edited Nov 26, 2017 at 2:45

Rick James

144k15 gold badges144 silver badges255 bronze badges

answered Nov 25, 2017 at 22:21

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Ryan Griggs Over a year ago

So you're saying the existing query is OK and there's not a more optimal way to achieve this?

Ryan Griggs Over a year ago

OK I added indexes and I'm blown away by the performance improvement! A query that originally took 5-8 seconds now takes 0.5 seconds! THANKS!

Ryan Griggs Over a year ago

To clarify, is this the best way to write this type of query? I saw some people using LEFT JOIN and GROUP BY statements, but this adds a level of complexity and problems since I need to return many/all fields in multiple joined tables.

Rick James Over a year ago

@RyanGriggs - "saw some people using..." -- Your situation seems to work well with the subqueries (with adequate composite indexes); some situations need LEFT JOIN and/or GROUP BY.

Gordon Linoff Over a year ago

@RyanGriggs . . . Your query is fine.

Collectives™ on Stack Overflow

MySQL: improving efficiency of sub-select query

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related