Solved

Mysql query - full table scan?

Posted on 2014-11-14
4
380 Views
Last Modified: 2014-11-14
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
0
Comment
Question by:doc_jay
4 Comments
 
LVL 9

Expert Comment

by:rawinnlnx9
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.
0
 
LVL 41

Accepted Solution

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

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

to

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

Assisted Solution

by:ste5an
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.
0
 

Author Comment

by:doc_jay
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', '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.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL (http://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL.html) several years ago, it seemed like now was a good time to updat…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Video by: Mark
This lesson goes over how to construct ordered and unordered lists and how to create hyperlinks.

895 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now