MySql query tooks 0.16 seconds in PhpMyAdmin, but 10 minutes in Php

Question

SELECT 
r.id randevu_id,
r.id,m.ad,m.soyad,r.musteri_id,m.telefon,
r.tarih,r.hizmet_id,
IFNULL((SELECT MAX(o.tarih) 
FROM odemeler o WHERE o.sil=0 AND o.randevu_id=r.id),"0000-00-00") sonodemetarih,
r.tutar borc
FROM randevular r
LEFT JOIN musteriler m ON m.id = r.musteri_id
WHERE r.sil = 0 AND r.randevu_durumu != 2

in PhpMyAdmin it tooks 0.6 seconds but in php with PDO it tooks 10 minutes. And when i delete this part

IFNULL((SELECT MAX(o.tarih) 
FROM odemeler o WHERE o.sil=0 AND o.randevu_id=r.id),"0000-00-00") sonodemetarih

it tooks 1 seconds in php

Why could it be? can you help me, Thanks.

Edit : you can only focus this query,

SELECT 
(SELECT count(1) FROM payments p WHERE p.appointment_id=ap.id AND p.sil=0 ) 
as paymentscount
FROM appointments ap

in PhpMyAdmin it tooks 0.6 seconds but in php with PDO it tooks 10 minutes.

Answer 1

What accounts for the high performance in myAdmin? Probably the query cache. When testing queries for performance use SELECT SQL_NO_CACHE ... or MySQL will remember the previous query result and give it to you again.

What accounts for the slow performance of the query? It has a dependent subquery, and MySQL is probably repeating that subquery for each row of the main query.

Try refactoring your query to avoid the dependent subquery, and instead to join to an independent summary subquery.

Your subquery is this:

             SELECT COUNT(*) payment_count, appointment_id
               FROM payments 
              WHERE sil=0
              GROUP BY appointment_id

It gives a little table of payment count by appointment.

Join that to the rest of your query:

 SELECT ap.*, pc.payment_count
   FROM appointments ap
   LEFT JOIN (
             SELECT COUNT(*) payment_count, appointment_id
               FROM payments 
              WHERE sil=0
              GROUP BY appointment_id
        ) pc ON ap.id = pc.appointment_id

This is faster because it only has to generate the subquery table once.

The knack you need here is generating the summary you need as a subquery, and using it as a virtual table.