0

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!

0

On files, add these compound indexes:

INDEX(filesystem_fk, file_status, file_tsuid, created_time),
INDEX(filesystem_fk, file_status, created_time)

(I am not sure which will help more.)

  • Rick - I've added a new index with an update to my question above with a new 'explain'. – Docjay Apr 1 '15 at 16:28
  • Notice how the t0_f "Rows" went from 74M down to 3M. That 25 times as good. (Of course, it may not translate into 25 times as fast.) Is it faster? – Rick James Apr 1 '15 at 17:47
  • If you need to discuss this further, please provide SHOW CREATE TABLE for each table, plus state about how big each table is. – Rick James Apr 1 '15 at 17:48
  • By adding the index, the application can finally do its job (which is to compress images). As it goes through its results the query will eventually get smaller, so I think that in the end, it will be very fast once it finishes all of it work in the coming weeks. Thank you! – Docjay Apr 2 '15 at 20:23

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.