Laravel: How to optimize multiple queries

Question

The queries on HomeController file slows down the site. It takes 20 seconds for the page to load fully. (Page size is 3.9 Mb only, and CPU load goes up to 80% every time when i load the page). I am told to use Query Builder which is faster than Elequant and join queries to send them as one query. I find this too difficult. Where can i see some examples for this ?

HomeController

public function index()
{
    $sliders = Post::where('post_type','slider')
        ->with('FeaturedImage','PostField')
        ->orderBy('created_at', 'desc')
        ->limit(4)
        ->get();

    $page1 = Post::where([
        ['post_type','=','custom_page'],
        ['slug','=','page1'],
    ])
        ->with('FeaturedImage','PostField')
        ->latest()
        ->first();


    $page2 = Post::where([
        ['post_type','=','custom_page'],
        ['slug','=','page2'],
    ])
        ->with('FeaturedImage','PostField')
        ->latest()
        ->first();


    $page3 = Post::where([
        ['post_type','=','custom_page'],
        ['slug','=','page-3'],
    ])
        ->with('FeaturedImage','PostField')
        ->latest()
        ->first();


    $compacts = array(
        'sliders',
        'page1',
        'page2',
        'page3',
    );
    return view('site.home')->with(compact($compacts));
}

edit: Post Migration

public function up()
{
    // Create table for storing roles
    Schema::create('posts', function (Blueprint $table) {
        $table->increments('id');
        $table->integer('author_id');
        $table->integer('category_id')->nullable();
        $table->string('title');
        $table->text('excerpt')->nullable();
        $table->text('body')->nullable();
        $table->string('slug')->nullable();//unique()
        $table->string('post_type')->default('post');
        $table->enum('status', ['PUBLISHED', 'DRAFT', 'PENDING'])->default('DRAFT');
        $table->timestamps();
    });
}

Where can i see some examples for this ? have you tried google? the laravel documentation? — madalinivascu
– madalinivascu, Commented Nov 2, 2017 at 6:16

patricus · Accepted Answer · 2017-11-02 07:29:51Z

2

You're running four queries against the posts table, and none of them are using indexes. This means four full table scans of the posts table. Additionally, you're sorting on an non-indexed field. This can cause performance issues as well.

You need an index on post_type and on slug for your query conditions. You can either create two individual indexes, or one composite index. It depends on your application needs.

$table->string('slug')->nullable()->index();
$table->string('post_type')->default('post')->index();

edited Nov 2, 2017 at 7:29

answered Nov 2, 2017 at 7:17

patricus

62.9k15 gold badges157 silver badges154 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

madalinivascu · Accepted Answer · 2017-11-02 06:28:34Z

1

You may want to do something like this to get all pages, and then split the pages using the collection groupBy() function

Post::where('post_type','=','custom_page')
->whereIn('slug',['page1','page2','page-3'])
->whereRaw('id IN (select MAX(id) FROM post GROUP BY slug)')
->with('FeaturedImage','PostField')
->get()
->groupBy('slug');

answered Nov 2, 2017 at 6:28

madalinivascu

32.4k4 gold badges41 silver badges59 bronze badges

Collectives™ on Stack Overflow

Laravel: How to optimize multiple queries

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related