时间戳

首页 > 解决方案 > MySQL中的查询加速

php - MySQL中的查询加速

问题描述

此代码收集并带来 100 万条记录的结果。这个过程需要很长时间才能显现出来。我怎样才能让它更快？

$urunler       = $db->get_results("SELECT urunid,urunadi FROM sf_urun01 WHERE sil!=2");
foreach ($urunler as $urun ){


$a   = $db->get_var("SELECT SUM(tonaj) FROM sf_urun10 WHERE urunid='$urun->urunid' AND (durumu='GIRIS' OR durumu='HATALI') AND cariid > 5 AND  sil !=2");
$b   = $db->get_var("SELECT SUM(tonaj) FROM sf_urun10 WHERE urunid='$urun->urunid'AND (durumu='GIRIS' OR durumu='HATALI') AND cariid < 5 AND istifyeri!='FASON'  AND   sil !=2");
$c   = $db->get_var("SELECT SUM(tonaj) FROM sf_urun10 WHERE urunid='$urun->urunid' AND durumu='HATALI' AND cariid < 5 AND  sil !=2");

$d   = $db->get_var("SELECT SUM(tonaj) FROM sf_urun10 WHERE urunid='$urun->urunid' AND istifyeri='FASON' AND (durumu='GIRIS' OR durumu='HATALI') AND cariid > 5 AND  sil !=2");


/*
Using Datatable 
*/

}

标签： phpmysql

解决方案

我认为您可以将所有这些查询合并为一个，这应该会更快：

SELECT urunid,
    SUM(
        CASE WHEN (durumu='GIRIS' OR durumu='HATALI') AND cariid > 5 THEN tonaj
        ELSE 0 END) 
    AS a,
    SUM(
        CASE WHEN (durumu='GIRIS' OR durumu='HATALI') AND cariid < 5 AND istifyeri!='FASON' THEN tonaj
        ELSE 0 END) 
    AS b,
    SUM(
        CASE WHEN durumu='HATALI' AND cariid < 5 THEN tonaj
        ELSE 0 END)
    AS c,
    SUM(
        CASE WHEN istifyeri='FASON' AND (durumu='GIRIS' OR durumu='HATALI') AND cariid > 5 THEN tonaj
        ELSE 0 END)
    AS d
FROM sf_urun10
WHERE sil !=2
GROUP BY urunid

这将给出一个表格

urunid  a   b   c   d
1       56  252 15  76
etc.

推荐阅读