Solved

Mysql query - full table scan?

Posted on 2014-11-14
4
375 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
Comment Utility
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
Comment Utility
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 32

Assisted Solution

by:Stefan Hoffmann
Stefan Hoffmann earned 250 total points
Comment Utility
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
Comment Utility
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

I use MySQL for many of my development projects in a Windows environment. To manage my databases (and perform queries) for years I used a tool called MySQL administrator.  This tool has since been replaced by MySQL Workbench. So I decided to m…
Creating and Managing Databases with phpMyAdmin in cPanel.
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

771 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

9 Experts available now in Live!

Get 1:1 Help Now