vendredi 30 octobre 2015

PHP Speeding Up MySql Queries

I am using the Laravel PHP Framework.

Say I have some queries like this:

public function order($orderby){
        \DB::connection()->disableQueryLog();

        if($orderby == "level"){
            $clan = Clans::orderBy('level', 'DESC')
            ->orderBy('exp', 'DESC')
            ->paginate(100,['id', 'clanid', 'name', 'level', 'exp', 'warwinpercent', 'warswon', 'playercount', 'score']);
        }elseif($orderby == "score"){
            $clan = Clans::orderBy('score', 'DESC')
            ->paginate(100,['id', 'clanid', 'name', 'level', 'exp', 'warwinpercent', 'warswon', 'playercount', 'score']);
        }elseif($orderby == "warwinpercent"){
            $clan = Clans::orderBy('warwinpercent', 'DESC')
            ->where('warswon', '>=', '100')
            ->paginate(100,['id', 'clanid', 'name', 'level', 'exp', 'warwinpercent', 'warswon', 'playercount', 'score']);
        }else
            $clan = Clans::paginate(100,['id', 'clanid', 'name', 'level', 'exp', 'warwinpercent', 'warswon', 'playercount', 'score']);

        \DB::connection()->enableQueryLog();

        return view('clans.index')->with('clan', $clan);
    }

These are taking around 10-15 seconds to run.

I have one like this:

public function index(){

        $clan = Clans::orderBy('clanid', 'ASC')
            ->paginate(100,['id', 'clanid', 'name', 'level', 'exp', 'warwinpercent', 'warswon', 'playercount', 'score']);

        return view('clans.index')->with('clan', $clan);
    }

Which loads almost instantly.

How come the first 3 are taking so much longer than the last one? I added an Index for all the variables I need to search through in my table but it is still taking ages for it to work. Do I need to do anything on the MySql side to make it rebuild the index? I already tried Optimize table and I restart the MySql Service multiple times as well..

If it isn't possible to speed them up then is there a simple way I can show a loading animation to the user while it loads the page?

Thanks!



via Chebli Mohamed

Aucun commentaire:

Enregistrer un commentaire