I have the following MySql query...
explain
select
PatNum as Id,
FName as FirstName,
LName as LastName,
Email,
Zip,
RIGHT(Replace(Replace(Replace(Replace(Replace(Replace(wirelessphone, ')', ''), '1(', ''), '(', ''), ' ', ''), '-', ''), '.', ''), 10) as Cell
from patient
WHERE EXISTS (select * from ProcedureLog where ProcedureLog.PatNum=Patient.PatNum and DateComplete=CurDate())
AND NOT EXISTS (select * from paysplit join payment on payment.paynum = paysplit.paynum where payment.patNum= patient.patnum and DatePay=CurDate() and payment.PayType = (select DefNum from definition where Category=10 and ItemName='Credit Card'))
The explain is showing....
The query works fine, 1 patient is returned.
The explain is showing for the patient table that 15 rows are being scanned?
However, between my exists and non exist where clause there is actually only one patient.
Shouldn't the query be evaluating the existing and non existing statements first and then only return 1 patient row in explain?
I am worried my query is going to table scan 1000s of records with a large patient table.
MySql Version: 5.5.14
Thank you in advance.