Slow query because of huge where in clause

Hey everybody!

I have an app with many employees which belong to different companies. Then I have supervisors who can access the data of multiple companies.

I do have 200,000 employees in the database (including soft deleted) and another 5000 companies.

My query looks like this.

Select * from employees where company_id in (22, 24, ....., 3000) and empoyees.deleted_at is null order by created_at desc limit 10 offset 0

Problem is for some supervisors you have access to the the data of more than 500 companies, the query is really slow when the where in block has so many company ids. Sometimes it takes more than 5 seconds....

Do you have an idea how to optimize that query or a different approach alltogether.

Thanks! :-)

LaryAI

2 years ago

Level 58

One possible solution to optimize the query is to use a subquery instead of a large IN clause.

Here's an example of how you can rewrite the query using a subquery:

SELECT * FROM employees 
WHERE company_id IN (SELECT id FROM companies WHERE supervisor_id = :supervisorId) 
AND employees.deleted_at IS NULL 
ORDER BY created_at DESC 
LIMIT 10 OFFSET 0

In this solution, you would replace :supervisorId with the actual ID of the supervisor who is accessing the data. The subquery (SELECT id FROM companies WHERE supervisor_id = :supervisorId) retrieves the IDs of the companies that the supervisor has access to, and then the main query filters the employees based on those company IDs.

This approach can be more efficient because the subquery will only return the necessary company IDs for the supervisor, rather than comparing against a large list of IDs in the IN clause.

Remember to replace supervisor_id with the actual column name in your database table.

I hope this helps! Let me know if you have any further questions.

JussiMannisto

2 years ago

Level 50

Do all relevant columns haved indexes: company_id, deleted_at and created_at? The company_id and created_at indexes are especially important. Otherwise the whole table will have to be scanned and sorted.

massimo

2 years ago

Level 16

@JussiMannisto It looks like it is only using one index (company_id). Pretty new working with Explain Select, it shows two possible keys but only one is used.

JussiMannisto

2 years ago

Level 50

@massimo Adding indexes for created_at and deleted_at should do the trick then. It's a simple migration:

// Up:
Schema::table('employees', function(Blueprint $table) {
	$table->index('created_at');
	$table->index('deleted_at');
});

// Down:
Schema::table('employees', function(Blueprint $table) {
	$table->dropIndex(['created_at']);
	$table->dropIndex(['deleted_at']);
}

Ps. If you're unsure if the columns already have indexes, you can check it by running the following SQL query:

show indexes from employees;

massimo

2 years ago

Level 16

@JussiMannisto They are displayed under possible keys, but only one key (company_id) is used. I ommited created_at in the query for now.

JussiMannisto

2 years ago

Level 50

@massimo Can you post the full output of your explain command?

Please or to participate in this conversation.