asked on

Mysql query - full table scan?

Mysql 5.6
CENTOS7
128GB RAM

Hi,

I'm coming from MSSQL 2008 and moving to Mysql. Currently trying to convert my queries to work with mysql. One query in particular wants to do a full table scan.

I run this on my Mysql Workbench 6.2

EXPLAIN EXTENDED
select distinct se.src_aet as "Ripped By", s.created_time as "Date/Time Sent", p.pat_name as "Patient Name", p.pat_id as "Patient ID", s.accession_no as "ACC #", p.pat_birthdate as "DOB", s.mods_in_study as "MOD", s.study_datetime as "Study Date", s.study_desc as "Study Desc", s.study_custom1 as "Inst Name"
from patient p
INNER JOIN study s
on p.pk = s.patient_fk
INNER JOIN series se
on s.pk = se.study_fk
where  s.accession_no like '%OUT%'
and DATE(s.created_time) = SUBDATE(CURDATE(), 1);

Open in new window

Explain results:

# id, select_type, table, type, possible_keys, key, key_len, ref, rows, filtered, Extra
'1', 'SIMPLE', 'p', 'ALL', 'PRIMARY', NULL, NULL, NULL, '651005', '100.00', 'Using temporary'
'1', 'SIMPLE', 's', 'ref', 'PRIMARY,patient_fk', 'patient_fk', '9', 'pacsdb.p.pk', '1', '100.00', 'Using where'
'1', 'SIMPLE', 'se', 'ref', 'study_fk', 'study_fk', '9', 'pacsdb.s.pk', '1', '100.00', NULL

Open in new window

Does this query need to be written differently? I'm not sure why it doesn't want to use the index on the 'patient' table.

If I have left out any info to help troubleshooting this, please let me know.

Thank you

rawinnlnx9

Full table scans are a result of not having proper primary and foreign keys defined. My first instinct would be to investigate your Inner Joins. Regardless, all tables should have an identity primary key or a unique primary key.

My bet is that if you break this query up and execute it separately you will quickly get to the heart of your issue. Do your select to your first inner join and investigate the results. Are you triggering a full scan? Break up each piece and make it granular.

Also execute procedures with some concrete test data. Test your joins individually. I.E. Inner Join t1.f1 = t2.f1 where t2.f1 = '[value]' and then investigate. Make sure you get expected results. Once satisfied investigate your next join separately. Test it's results. What I mean here is look at the results of your two joins separately first. Then run them together. Do the results of the separate joins line up with you expect when you include them both in the execution?

Many times working in SQL is like building with blocks. Make sure each block works by itself before adding another block to it. Become familiar with your data and what you expect so you can spot anomalous behavior.

ASKER CERTIFIED SOLUTION

ralmada

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

ste5an

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

doc_jay

ASKER

by changing the last line with 'created_time' did the trick.

Here is the explain now:

# id, select_type, table, type, possible_keys, key, key_len, ref, rows, filtered, Extra
'1', 'SIMPLE', 's', 'range', 'PRIMARY,patient_fk,study_created', 'study_created', '6', NULL, '329', '100.00', 'Using index condition; Using where; Using temporary'
'1', 'SIMPLE', 'p', 'eq_ref', 'PRIMARY', 'PRIMARY', '8', 'pacsdb.s.patient_fk', '1', '100.00', NULL
'1', 'SIMPLE', 'se', 'ref', 'study_fk', 'study_fk', '9', 'pacsdb.s.pk', '1', '100.00', NULL

Open in new window

the results come back instantly!

thank you ralmada & ste5an!

--also thanks to rawinnlnx9 for the idea of breaking up the query to help find what might be slowing it down.