How to replace whereHas because it is too slow?

Question

MySQL

Now there is this code that displays all the articles in the desired category:

$articles = Article:with('types')->whereHas('categories', function ($query) use ($name) {
            $query->where('name', $name);
})->paginate(10);

But there are a lot of records in the table and it takes a long time, I found a solution in Google, use whereRaw ( https://stackoverflow.com/a/36384966)
But I can’t change it for myself, in this example, the selection occurs by category id, but I have it goes by name, i.e. you need to additionally get the category id by its name from the categories table, but how?

Answer 1

If you think about it, two simple and fast queries are better than one heavy one.
In any case, I think you will still need the category / s separately in the template somewhere, so you can first get it / them by name, and then get the articles

$categories = Category::whereName($name)->get();
$articles = Article:with('types')->whereIn('id', $categories->pluck('id'))->paginate(10);

If you insist on a single query, you will have to use Joins, which will also have a bad effect on performance.

Answer 2

whereHas makes the usual exists. It's not even close to being slow. The problem is the lack of indexes (on categories.name and on columns linking two tables).