Solved

Mysql query - full table scan?

Posted on 2014-11-14
4
386 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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This guide whil teach how to setup live replication (database mirroring) on 2 servers for backup or other purposes. In our example situation we have this network schema (see atachment). We need to replicate EVERY executed SQL query on server 1 to…
Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

679 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