Slow MySQL query with Laravel Eloquent

Are you using indexing, there was just a discussion on this, probably on very same page.

See this other very recent thread: https://laracasts.com/discuss/channels/laravel/laravel-query-tooo-slow

Yes, I'm using indexes as you can see in the image with the explain. I really fell I'm missing something here.

I read a lot of posts about slow queries, but this one in particular I could not resolve.

In short, these all need to be indexed in your migrations:

custom_products.hidden
custom_products.company_id
categorizables.category_id
categorizables.categorizable_type
categories.deleted_at
images.imageable_id
images.imageable_type
images.deleted_at
custom_products.deleted_at

I'm sure there are a lot of other fields that need it too, but this is what's affecting the stated query. It has purely to do with mysql and nothing to do with Laravel or Eloquent. You can take that same query and run it directly and it will be just about as slow without the proper mysql indexes.

How many records is this query dealing with.

Yes, I'm using indexes as you can see in the image with the explain.

It looks like you only have a single index, when you really need many more.

Anything that is used in a WHERE needs to be indexed if there is a lot of data in the tables.

Please watch these videos: https://serversforhackers.com/laravel-perf/mysql-indexing-one

I would make more tables and break down that query some. You may want to look into getting a result in a temp table then query the temp table. Even with indexing that is a huge query.

Even have a totals or count table setup that you can just run queries on a lesser amount of data.

For example, you want results over 3 months, but have a huge record set.

• query june store in table
• query july store in table
• query august store in table

Add the 3 results now in another query.

To me it is easier to break down large into a series of smaller logical steps. Just my 2 cents.

Your code formatted

select count(*) as aggregate from custom_products where hidden = '0' and (company_id = '1') and exists ( select * from categories inner join categorizables on categories.id = categorizables.category_id where custom_products.id = categorizables.categorizable_id and categorizables.categorizable_type = 'App\Models\CustomProduct' and categories.deleted_at is null) and exists ( select * from images where custom_products.id = images.imageable_id and images.imageable_type = 'App\Models\CustomProduct' and images.deleted_at is null) and custom_products.deleted_at is null

Makes it easier to read. More pretty also.

Actually you don't need to create indexes in fields with low cardinality like 'deleted_at' and 'hidden'.

You do if you want to speed something like this up: WHERE ... and images.deleted_at is null

Please watch the videos I linked. He's using laravel and shows the before and after effects when adding the indexes to the correct places, including updated_at/created_at, which would also apply to deleted_at when used in a WHERE.

@rafito Your query is doing a count and checking if 2 other selects have any rows? Since I don't know your data sets why are you doing the exists at all? Either the count result will be zero or > 0. The 2 exist selects seem to filter a result set? What is the main driving table in relation to these 2 exist sub-queries? Just creating indexes to speed up a query can hurt the overall performance of your database. Proper indexes will help the optimizer limit the table scan, too many indexes can force a full table scan. Do SQL Explain plan on this query to see what the optimizer will do.

@jimmck You were gone for a while, I hope you are doing well.