Mysql – How to combine these two queries and have them be as fast

MySQL

I have a very legacy system with millions upon millions of records. It is very poorly managed and maintained, originally having been an Access database.

So in one particular look-up I am getting the asset deployment history for a particular employee. Their name is searched in the employee table to find their Employee ID. Their employee ID is then searched in the PC history table.

Right now I have two queries that are fast:

SELECT `tbl_employee`.EMPLID 
FROM `tbl_employee` 
WHERE `tbl_employee`.NAME 
LIKE "%Smith,John%"

The above query takes 0.0250 to 0.0850 seconds to resolve in benchmarks. In this case the employee ID is 518266.

I can re-use this result in the next query:

SELECT `tbl_pc history`.SerialNumber, `tbl_pc history`.Date, `tbl_pc history`.ModelID 
FROM `tbl_pc history` 
WHERE `tbl_pc history`.`EmployeeNumber` 
LIKE "%518266%";

This is also very fast, between 0.0340 and 0.0679 seconds in benchmarks.

I now arrive at what I want, a list of 4 items from millions which were at some point issued to this employee.

I have tried to combine this query a couple of ways and they resulted in several second resolution times, instead of fractions of a second:

SELECT `tbl_pc history`.SerialNumber, `tbl_pc history`.Date, `tbl_pc history`.ModelID 
FROM `tbl_pc history` 
WHERE `tbl_pc history`.`EmployeeNumber` 
IN 
    (SELECT tbl_employee.EMPLID 
    FROM tbl_employee 
    WHERE tbl_employee.NAME 
    LIKE "%Smith,John%");

The above query gets me what I want, but takes 4.8508 to 6.3010 seconds to execute.

SELECT `tbl_pc history`.`SerialNumber`, `tbl_pc history`.`Date`, tbl_employee.NAME, `tbl_pc history`.`ModelID` 
FROM `tbl_employee` 
INNER JOIN `tbl_pc history` 
ON `tbl_pc history`.`EmployeeNumber` = tbl_employee.EMPLID 
WHERE `tbl_employee`.`NAME` 
LIKE "%Smith,John%"

This also results in 4-6 second queries.

Please excuse my ignorance in this area – I have next to no knowledge of databases, SQL, PHP, or any of this and have been blindly googling my way to solutions for the past couple of weeks.

Best Answer

Would help if you post SHOW CREATE TABLE for the two tables and also how many rows each table has.

Most likely the slowness is due to use of LIKE "%Smith,John%". This will do a full table scan on tbl_employee.

Try LIKE "Smith,John%" just to see if the query performance is any better this way. I am assuming you have an index on tbl_employee.name column.

Related Question