Improve many-to-many Query Performance with Pagination using Laravel 4.2

Question

I have what seems like an easy many-to-many relationship query with pagination. It works fine, but the downside is the time it takes. On the prod server, it's more than 20 seconds. On my development environment, 13 seconds.

Here is the code:

$query = $this->excerpt->orderBy($sort, $order);
$excerpts = $query->with('source.authors')
            ->with('excerptType')
            ->with('tags')
            ->whereHas('tags', function($q) use ($tagId){
                $q->where('tag_id', $tagId);
            })
            ->paginate($this->paginateCount);

These two queries take the longest

select count(*) as aggregate
    from `excerpt`
    where (select count(*)
            from `tags`
            inner join `excerpt_tag`
                  on `tags`.`id` = `excerpt_tag`.`tag_id`
            where `excerpt_tag`.`excerpt_id` = `excerpt`.`id`
                 and `tag_id` = '655') >= 1

2.02 secs

select *
    from `excerpt`
    where (select count(*) from `tags`
            inner join `excerpt_tag`
                    on `tags`.`id` = `excerpt_tag`.`tag_id`
            where `excerpt_tag`.`excerpt_id` = `excerpt`.`id`
              and `tag_id` = '655') >= 1
    order by `created_at` desc limit 15 offset 0

2.02 secs

I was thinking of changing this to a simple query with inner joins, like:

select *
    from `excerpt`
    inner join excerpt_tag  on excerpt.id = excerpt_tag.excerpt_id
    inner join tags  on excerpt_tag.tag_id = tags.id
    where tags.id = 655
    limit 10  offset 0

But then I lose the advantage of eager loading and so on.

Does anyone have an idea on what the best way to speed this up would be?

Answer 1

Change

( SELECT COUNT(*) ... ) > 0

to

EXISTS ( SELECT 1 ... )

Follow the instructions here for index tips in many:many tables.

If a tag is just a short string, don't bother having a table (tags) for them. Instead, simply have the tag in the excerpt_tag and get rid of tag_id.

A LIMIT without an ORDER BY is somewhat meaningless -- which 10 rows you get will be unpredictable.

Answer 2

Well I have a solution that has led to a significant improvement and only added a few lines of code and only 1 or maybe 2 extra sql queries.

I decided to query the tags first and find out which excerpts were connected and then use a whereIn to then query all information from the excerpts, thus hoping to still make use of the with function and eager loading. At least keep the number of queries down to an absolute minimum.

Here is the code with the solution:

    // workaround to make excerpt query faster
    $excerptsWithTag = $this->tag->with(['excerpts' => function($query) {
        $query->select('excerpt.id');
    }])->find($tagId,['tags.id']);
    // actual excrpt query
    $excerptIds = array_column($excerptsWithTag->excerpts()->get()->toArray(), 'id');
    $query = $this->excerpt->orderBy($sort, $order);
    $excerpts = $query->with([
            'source.authors',
            'excerptType',
            'tags'
        ])
        ->whereIn('excerpt.id', $excerptIds)
        ->paginate($this->paginateCount);

There is very likely a much more eloquent way to solve this problem, but this works and I'm happy.