🐥 Optimising large whereIn queries in Laravel

When you use the whereIn clause in Laravel Eloquent, you might end up hitting the maximum number of parameters that your database engine supports. Depending on the database engine you use, the maximum number of parameters it supports might be as low as 2100 (Microsoft SQL Server).

On top of that, there is an old bug in the PDO implementation for MySQL which can cause these types of queries to be very slow.

If you are using an array of integers in the clause, you're lucky. In that scenario, you can use the whereIntegerInRaw function instead.

So, instead of doing this:

User::whereIn('id', [1, 2, 3, 4, 5])->get();

You can simply use:

User::whereIntegerInRaw('id', [1, 2, 3, 4, 5])->get();

The difference is in the way the SQL statement is generated.

When using whereIn, the SQL will become (needing 5 placeholders):

SELECT * FROM users WHERE id in (?, ?, ?, ?, ?)

When using whereIntegerInRaw, the SQL becomes (values are in the statement, no placeholders needed):

SELECT * FROM users WHERE id in (1, 2, 3, 4, 5)

The whereIntegerInRaw method ensures that the values of the array are integers, which ensures there are no SQL injection vulnerabilities. This is the reason why this workaround only works for an array of integers. This shouldn't be a problem as using integers in a whereIn clause probably covers the majority of the use cases.