MySQL Query Optimization – Improving Performance

MYSQL version 5.6.23

CENTOS 7

Hi,

I have the following query that is coming from an application that I am hoping to make more efficient from the mysql side of things.

I have an index built on each of the columns in tables 'files', 'filesystem', and 'instance'.

SELECT t0_f.pk, t0_f.created_time, t0_f.md5_check_time, t0_f.filepath, t0_f.file_tsuid, t0_f.file_md5, t0_f.file_status, t0_f.file_size, t0_f.instance_fk, t0_f.filesystem_fk 
FROM files t0_f, filesystem t1_f_fileSystem, instance t2_f_instance
WHERE (t0_f.file_status = 0 
AND t0_f.file_tsuid 
IN ('1.2.840.10008.1.2', '1.2.840.10008.1.2.1', '1.2.840.10008.1.2.2') 
AND t1_f_fileSystem.pk = 1 
AND t2_f_instance.sop_cuid = '1.2.840.10008.5.1.4.1.1.2' 
AND t0_f.created_time < '2015-03-26 09:29:58' 
AND t0_f.filesystem_fk=t1_f_fileSystem.pk 
AND t0_f.instance_fk=t2_f_instance.pk)

Here is the explain:

# id, select_type, table, type, possible_keys, key, key_len, ref, rows, filtered, Extra
'1', 'SIMPLE', 't1_f_fileSystem', 'const', 'PRIMARY', 'PRIMARY', '8', 'const', '1', '100.00', 'Using index'
'1', 'SIMPLE', 't0_f', 'ref', 'instance_fk,filesystem_fk,file_tsuid,file_created,file_status', 'filesystem_fk', '9', 'const', '74220028', '100.00', 'Using where'
'1', 'SIMPLE', 't2_f_instance', 'eq_ref', 'PRIMARY,sop_cuid', 'PRIMARY', '8', 'pacsdb.t0_f.instance_fk', '1', '100.00', 'Using where'

Would this maybe benefit from a 'combined' index or would there be any other way to improve the 'explain' short of changing the query as I have no control over that.

If there is anything else that I can post to help, please let me know!

thanks

UPDATE

I created an index 'files_compress' (the first index you suggested) and its estimate went down to 3.1M rows with a 'Index Range Scan'. I added the 2nd one as well, but it didn't change the results.

Here is the new 'Explain'

# id, select_type, table, type, possible_keys, key, key_len, ref, rows, filtered, Extra
'1', 'SIMPLE', 't1_f_fileSystem', 'const', 'PRIMARY', 'PRIMARY', '8', 'const', '1', '100.00', 'Using index'
'1', 'SIMPLE', 't0_f', 'range', 'instance_fk,filesystem_fk,file_tsuid,file_created,file_status,files_compress,files_compress_2', 'files_compress', '772', NULL, '3192823', '100.00', 'Using index condition; Using where'
'1', 'SIMPLE', 't2_f_instance', 'eq_ref', 'PRIMARY,sop_cuid', 'PRIMARY', '8', 'pacsdb.t0_f.instance_fk', '1', '100.00', 'Using where'

Do you think there is any more room for improvement? I know that its way better than 74M rows!

thanks again!