I'm wondering if someone can explain how the IN calculates? Well, ultimately I'm trying to find out why this query is slow and how to optimize it. I waited over 3 minutes and when I cancelled the query it had only returned 1000 lines which doesn't seem like it should take that long.
SELECT t2.*
FROM report_tables.roc_test_results as t2
WHERE t2.job IN (SELECT DISTINCT(t1.job)
FROM report_tables.roc_test_results as t1
WHERE t1.operation = 'TEST'
AND result = 'Passed'
AND STR_TO_DATE(t1.date_created,'%d-%M-%Y') BETWEEN '2009-10-01'
AND '2009-10-31')
I'm not sure what the total query should return, if I had to guess I would say around 2000 records, the subquery returns 332 (336 when not Distinct).
Can anyone give me some pointers on how to optimize this query? Also, I'm wondering, does the subquery calculate every time or just once and store it?
As requested, the results for DESC... (by the way, please don't laugh, I am self taught so I'm sure this table is hideously designed.)
Field Type Null Key Default Extra
------ ----- ----- --- ------- -----
operation varchar(10) NO
tester varchar(25) NO
result varchar(45) NO
fail_mode varchar(45) NO
primary_failure varchar(25) NO
ref_des varchar(45) NO
rf_hours varchar(15) NO
ac_hours varchar(15) NO
comments text NO
job varchar(15) NO
rma bigint(20) unsigned NO
item varchar(45) NO
item_description text NO
serial varchar(25) NO
created_by varchar(25) NO
collection bigint(20) unsigned NO PRI
date_created varchar(15) NO
The date_created data type needs to change to be a DATETIME before it's worth defining an index on the column. The reason being, the index will be worthless if you are changing the data type from string to DATETIME as you are currently.
You've mentioned that you're using LOAD DATA INFILE, and that the source file contains dates in DD-MON-YY format. MySQL will implicitly convert strings into DATETIME if the YY-MM-DD format is used, so if you can correct this in your source file before using LOAD DATA INFILE the rest should fall in to place.
After that, a covering index using:
...would be a good idea.