Tricky SQL Query Help

General
1

I’m usually pretty good at figuring out efficient ways to query my databases, but I’m completely stuck on how to get the info I need without a slow subquery. Hoping someone here can help:

First off, this is running in a mysql database.

I have 2 tables: hardware, and hardware_to_license.

[code]hardware:
id
serial_number

hardware_to_license
id
hardware_id
license_id[/code]

There is also a license table, but that is not needed for this query. These 3 tables tie specific devices (hardware) to specific licenses. A license can have multiple pieces of hardware tied to it, so I have the hardware_to_license tying table.

Here is what I need: Given a serial number, find all the serial numbers of devices attached to the same license.

My subquery approach looks something like this:

select h.serial_number from hardware h, hardware_to_license htl where htl.hardware_id = h.id and htl.license_id in (
  select htl2.license_id from hardware h2, hardware_to_license htl2 where h2.serial_number = '123456'
)

The above works, but can be VERY slow, since there are potentially many thousands of devices and many thousands of licenses, with many more thousands of entries in the hardware_to_license table, and the subquery forces a full table scan on every matching row in the hardware_to_license table.

My intuition tells me there is a nasty incestuous type of join I should be able to do here, but none of my sql-fu so far has yielded any correct results. This one appears to be very close, but lists the same serial number 3 times (there are 3 pieces of hardware attached to this specific license:

select h.serial_number from hardware h left join hardware_to_license htl on htl.hardware_id = h.id left join hardware_to_license htl2 on htl.license_id = htl2.license_id where h.serial_number = 'AE3319GC';

If you are still reading… erhm… help?

2

I don’t see what you’re getting out of htl2. This looks like a simple inner join between 2 tables?

select h.serial_number from hardware h inner join hardware_to_license htl on htl.hardwareid = h.id where h.serial_number = '1234';
3

Thanks @Wayne Golding - when I run your query it is only returning a single row (matching the serial number I specify at the end of the query). But I like where you are going with this, and you are right - it should just be a join with the 2 tables.

4

Indexing?

5

Thanks @Eli Ott - I just added indexes to both tables, and the speedup is massive. Most queries are taking about 62ms to run. If possible, I need to speed it up even more because I’ll potentially need to run this query several hundred times (checking several hundred serial numbers) at a time. Would joins be faster?

6 
SELECT hardware_to_license.hardware_id, hardware.serial_number FROM hardware_to_license, hardware WHERE hardware_to_license.hardware_id = hardware.id AND hardware_to_license.license_id = '123456'
7

Indexes on all the ID fields, AND the serials. (Always index a field you want to filter on, is my advice)

Eli’s joined query as above.

Put the several hundred serials into a temp table.

And join to that table, so that you get all the values returned for all serials in a single query.
Then iterate over the result set

That should be faster than calling the same query hundreds of times.

SELECT hardware_to_license.hardware_id, hardware.serial_number FROM hardware_to_license, hardware, tableofserials WHERE hardware_to_license.hardware_id = hardware.id AND hardware_to_license.license_id and tableofserials.serial_number = hardware.serial_number order by tableofserials.serial_number ;

8

Hello.

you use derived tables

Select hardware.serial_number
From hardware Inner Join
hardware_license on hardware.hardware_id = hardware_license.hardware_id Inner Join
(
Select hl.license_id
From hardware h Inner Join
hardware_license hl on h.hardware_id = hl.hardware_id
Where h.serial_number = ‘AE3319GC’
) t on hardware_license.license_id = t.license_id

Saludos.
Mauricio

9

Couple of ideas…

  1. A compound index on the columns hardware_to_license.license_id & hardware_to_license.hardware_id might help.

  2. Try using the SQL IN operator so that you can search for multiple license IDs at the same time (you might want to include that column in the result set so that you can identify which rows belong to each license).

  3. If you are using InnoDB, make sure you have allocated adequate RAM to the buffer pool.