Laravel数据库MySQL查询需要很长时间

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了Laravel数据库MySQL查询需要很长时间，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含3538字，纯文字阅读大概需要6分钟。

内容图文

我有一个连接到我的数据库的页面,但收集所有数据后页面加载大约需要1分钟.

有什么我做错了,或者我能做些什么来加快这个过程？

调节器

class ReportSummaryController extends Controller
{
    /**
     * Display a listing of the resource.
     *
     * @return \Illuminate\Http\Response
     */

    public function __construct()
    {
        $this->middleware('auth');
        $user = Auth::user();

        if (@$user->name)
            $details = DB::table('taffiliate')
                ->where('affID', "=", $user->name)
                ->get();
        else
            return redirect('/login');

        view()->share('details', $details);
    }

    public function index()
    {


        $user = Auth::user();
        $affiliate = $user->name;


        $affdata = DB::table('toutcome')->select(DB::raw('sum(LenderCommission) as LenderCommission'), 'AffID', 'AppID')
            ->whereRaw('CompletedDate >= curdate()')
            ->groupBy('AffID')
            ->orderBy('AppID', 'ASC')
            ->get();



        $data3 = DB::table('toutcome')

            ->where('CompletedDate', '>=', \Carbon\Carbon::now()->startOfMonth())
            ->whereRaw('AffID Not Like "MW0050"')

            ->join('tapplicant', 'toutcome.AppID', '=', 'tapplicant.AppID')
            ->select(DB::raw('DATE_FORMAT(CompletedDate, "%d %M %Y") as CompletedDate, 
                              SUM(LenderCommission) as commission, 
                              SUM(AffCommission) as affCommission,
                              COUNT(DISTINCT tapplicant.Email) as leadcount,
                              SUM(Status = "A" AND LenderCommission Not Like "0.00") as acceptcount'))

            ->groupBy(DB::raw('DATE_FORMAT(CompletedDate, "%d %M %Y")'))
            ->get();



        $users = Toutcome::where('CompletedDate', '>=', \Carbon\Carbon::now()->startOfMonth())

            ->join('tapplicant', 'toutcome.AppID', '=', 'tapplicant.AppID')
        ->select(DB::raw('DATE_FORMAT(CompletedDate, "%d %M %Y") as CompletedDate, 
                              SUM(LenderCommission) as Commission, 
                              SUM(AffCommission) as Affiliate_Commission, 
                              COUNT(DISTINCT tapplicant.Email) as Applications,
                              SUM(Status = "A" AND LenderCommission Not Like "0.00") as Sold'))
            ->whereRaw('AffID Not Like "MW0050"')
            ->groupBy(DB::raw('DATE_FORMAT(CompletedDate, "%d %M %Y")'))
            ->get();

$comtotal = DB::table('toutcome')

    ->where('CompletedDate', '>=', \Carbon\Carbon::now()->startOfMonth())
    ->whereRaw('LenderCommission Not Like "0.00"')

    ->sum('LenderCommission');

        $subid = DB::table('tapplicant')
            ->whereRaw('AppAffID Not Like "050"')
            ->whereRaw('AppAffID Not Like "000"')
            ->where('AppDate', '>=', \Carbon\Carbon::now()->startOfMonth())
            ->select('AppAffID')
            ->groupBy('AppAffID')
            ->get();

        $lender = DB::table('toutcome')
            ->select('LenderName')
            ->groupBy('LenderName')
            ->get();

        $imtotal = DB::table('toutcome')
            ->where('CompletedDate', '>=', \Carbon\Carbon::now()->startOfMonth())
            ->whereRaw('LenderCommission Not Like "0.00"')
            ->whereRaw('AffID Not Like "0050"')
            ->count('AppID');

        $cototal = DB::table('toutcome')
            ->where('CompletedDate', '>=', \Carbon\Carbon::now()->startOfMonth())
            ->whereRaw('LenderCommission Not Like "0.00"')
            ->whereRaw('AffID Not Like "0050"')
            ->where('Status', '=', 'A')
            ->count('AppID');

        $comtotal2 = DB::table('toutcome')
            ->where('CompletedDate', '>=', \Carbon\Carbon::now()->startOfMonth())
            ->whereRaw('LenderCommission Not Like "0.00"')
            ->sum('LenderCommission');

        $comtotal3 = DB::table('toutcome')
            ->where('CompletedDate', '>=', \Carbon\Carbon::now()->startOfMonth())
            ->whereRaw('LenderCommission Not Like "0.00"')
            ->sum('AffCommission');

return view('summary', compact('affdata','data3', 'comtotal', 'subid' , 'users', 'lender', 'imtotal', 'cototal', 'comtotal2', 'comtotal3'));



    }

解决方法:

首先,听起来真的很长.

查询看起来非常详细,但不应超过1分钟.

您可以尝试使用eloquent,但这只会比原始查询快一点.

你没有提到的事情是：

这是本地服务器还是远程服务器？
如果您使用的是远程服务器,我的解决方案是在mysqld下的my.ini / my.cnf中使用“skip-name-resolve”并更新您的key_buffer_size.

如果这不能提高速度,可以查看该特定服务器的资源.

内容总结

以上是互联网集市为您收集整理的Laravel数据库MySQL查询需要很长时间全部内容，希望文章能够帮你解决Laravel数据库MySQL查询需要很长时间所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端