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
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.