0

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.

  • 2
    Please tag your MySql version, and add the table schema plus index. – McNets Dec 15 '17 at 8:00
0

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.

0

Few recommendations:

  1. Avoid leading wildcard in LIKE searches if you can. Will it be enough to search for LIKE "%Smith,John%" instead of LIKE "%Smith,John%"? If not, consider full text indexing.
  2. Make sure you have the relevant indexes in place:

    ALTER TABLE tbl_employee ADD INDEX tbl_employee_index_1 (EMPLID, NAME); ALTER TABLE tbl_pc history ADD INDEX tbl_pc history_index_1 (EmployeeNumber, SerialNumber, Date, ModelID);

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.