How to optimize my SQL query

@reinisk22 is it slow in production or slow in development?

if you run the query directly in MySQL does it take the same amount of time to run it?

I've seen reports of slow queries and it very often comes down to issues with local windows development environments.

switching to use homestead or docker usually improves this.

@reinisk22 have you tried using indexes in MySQL? This can improve your query performance significantly.

Resources: https://laracasts.com/series/mysql-database-design/episodes/13 https://www.youtube.com/watch?v=HubezKbFL7E

Basically, I think you need to index strategy and assigned_at column and also another column for your count() function because that needs to be index as well. probably the best is to select count(id) ... instead of select count(*) so you can index your id column (assuming you have id column in your table)

@automica It's slow in production. I don't fully understand why my manager wants to optimize this tho, I ran the query against prod DB and it took about 2.5 seconds which isn't that bad and the query isn't being run very often.

@neilstee I just checked the DB tables. It seems like they are indexed, but then again, I don't really know much about it, will take a look at the suggested videos, thanks!

Some images of the table setup might help us get some ideas as to how it is set up, and how you can improve it.

@sinnbeck Hey, added some pictures to the post

@reinisk22 can you EXPLAIN the query?

explain select count(*) as aggregate from `external_ids` where `strategy` = 'very long string' and `assigned_at` is not null;

what about

->whereNotNull('assigned_at')
	->where('strategy', (string) $strategy)
        ->count('id');