MySql查询在phpadmin中为0.16秒，在Php中为10分钟

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

phpadmin myit的时间是0。6秒，php的时间是10分钟。当我删除这部分

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

它在php中只有1秒

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.

phpadmin myit的时间是0。6秒，php的时间是10分钟。

1 个解决方案

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

什么原因导致myAdmin的高性能?可能是查询缓存。当测试性能查询时，请选择SQL_NO_CACHE…或者MySQL会记住之前的查询结果并再次给出。

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.

是什么导致了查询的缓慢性能?它有一个独立的子查询，MySQL可能会对主查询的每一行重复这个子查询。

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.

这里需要的技巧是生成作为子查询所需的摘要，并将其用作虚拟表。

#1