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 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);
# 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
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.