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.