Forum slow query

posted 11 years ago

select * from `posts` where (select count(*) from `categories` inner join `post_categories` on `categories`.`id` = `post_categories`.`category_id` where `post_categories`.`post_id` = `posts`.`id` and `name` = '大標題' order by `categories`.`id` asc) >= '1' and `published_at` = '2014-03-18'


$headlines = Post::with('categories')->whereHas('categories', function($query)
{
    $query->where('name', '=', '大標題');
    $query->orderBy('categories.id', 'asc');
})->where('published_at', '=', Session::get('date'))->get();

this query is slow and it takes 27.27s to complete (based on debug bar)

anybody know why? or is there anyway to improve it?

Last updated 3 years ago.

marwelln

replied 11 years ago

Show us what

explain select * from `posts` where (select count(*) from `categories` inner join `post_categories` on `categories`.`id` = `post_categories`.`category_id` where `post_categories`.`post_id` = `posts`.`id` and `name` = '大標題' order by `categories`.`id` asc) >= '1' and `published_at` = '2014-03-18'

gives you.

You are most likely missing indexes.

posts table should have one index for published_at.
categories should have an index for name.
post_categories should have two foreign keys, first on post_id matching posts.id, second on category_id matching categories.id.