Why is Eloquent join with pivot table very slow?

Hi,

I have 3 tables:

news
category
news_category (news_id and category_id)

News model:

public function category()
{
    return $this->belongsToMany('App\Models\Category', 'news_category', 'news_id', 'category_id');
}

Category model:

public function news()
{
    return $this->belongsToMany('App\Models\News', 'news_category', 'category_id', 'news_id');
}

I need to retrieve news that belong in a certain categories. I do it like this:

$news = News::whereHas('category', function ($query) use($ids) {
                $query->whereIN('category_id', [9, 10, 11, 24]);
            })
                ->orderBy('publish_date', 'desc')
                ->take($limit)
                ->get();

$limit is usually 6. The problem is that when the news table had less than 1000 records on test database, it worked fast. On the production database we have more than 150.000 records and in that case this gets stuck for a very long time. If I do this query with the query builder it works normally and fast, so it must be the way Eloquent gets the data. Any idea how to make this faster with Eloquent?

jlrdw

9 years ago

Level 75

Normal pdo is going to be faster than active record, but naturally the more records the longer any query will take. That's why most large enterprise don't mess with active record, normally you see an implementation of hibernate.

jimmck

9 years ago

Level 13

@andrejmk I see no join statement in your code? First look at the sql that is generated. Also how many actual queries are generated. In straight SQL (which is what active record gets transformed into) its a single query. Chances are Eloquent is solving this via the N+1 problem way and ~3 queries are being run and then the data is being matched manually instead of letting the database do the work. There is no such thing as normal PDO as all of your database requests run through PDO.

Install DebugBar
Use it to see your queries
Look at the resulting SQL.
Use Query builder to add joins to remove the N+1 performance hit.

select * from news, category where
news.id = category.news_id  <-- In SQL is this an implied Join.
and category.id in (9, 10, 11, 24)

Learn SQL so you can know when to modify generated code. All ORM's like Elouquent generate 1 or more SQL statements and execute them through a PDO connection. ORM's have no secret pathway to the database or run special code above and beyond the database. PDO has no secret connections to the database. It generalizes the network interface a program needs to execute instructions (SQL) to the database.

The optimal number of queries is always 1, when ever possible "Push the work down into the database". The query optimizer is designed to (normally) be efficient in the searching and sorting needed to resolve multiple table queries. When in doubt run a SQL plan on generated query.

Please or to participate in this conversation.