Mysql query - full table scan?

Posted on 2014-11-14
Last Modified: 2014-11-14
Mysql 5.6


   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

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 = s.patient_fk
INNER JOIN series se
on = 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', '', '1', '100.00', 'Using where'
'1', 'SIMPLE', 'se', 'ref', 'study_fk', 'study_fk', '9', '', '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
Question by:doc_jay

Expert Comment

ID: 40443562
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.
LVL 41

Accepted Solution

ralmada earned 250 total points
ID: 40443570
I would also suggest changing

and DATE(s.created_time) = SUBDATE(CURDATE(), 1);


and s.created_time >= SUBDATE(CURDATE(), 1) and s.created_time < curdate();
LVL 33

Assisted Solution

ste5an earned 250 total points
ID: 40443574
The DATE() and the LIKE '%OUT%' predicate are both not sargeable. You cannot do anything about the LIKE. But you can change the DATE() part. Place the logic on the right side, something like s.create_date between curdate() and curdate()+interval 1 day.

Author Comment

ID: 40443633
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', '', '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.

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have been using r1soft Continuous Data Protection ( for many years now with the mySQL Addon and wanted to share a trick I have used several times. For those of us that don't have the luxury of using all transact…
Introduction This article is intended for those who are new to PHP error handling (  It addresses one of the most common problems that plague beginning PHP develop…
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

776 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question