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?
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.