The question:
I noticed a query that seemed to take way too long most of the time, so I investigated it with the Laravel query logger (to get the time and the actual SQL from the query builder).
Excerpt:
>>> DB::flushQueryLog(); DB::enableQueryLog(); Company::findAllByNameOrAlias('Frankfurt University of Applied Sciences'); DB::getQueryLog()
=> [
[
"query" => "select * from `companies` where (`name` = ? or exists (select * from `company_aliases` where `companies`.`id` = `company_aliases`.`company_id` and `name` = ?)) and `companies`.`deleted_at` is null",
"bindings" => [
"Frankfurt University of Applied Sciences",
"Frankfurt University of Applied Sciences",
],
"time" => 278.46,
],
]
>>> DB::flushQueryLog(); DB::enableQueryLog(); Company::findAllByNameOrAlias('Frankfurt University of Applied Sciences'); DB::getQueryLog()
=> [
[
"query" => "select * from `companies` where (`name` = ? or exists (select * from `company_aliases` where `companies`.`id` = `company_aliases`.`company_id` and `name` = ?)) and `companies`.`deleted_at` is null",
"bindings" => [
"Frankfurt University of Applied Sciences",
"Frankfurt University of Applied Sciences",
],
"time" => 0.72,
],
]
>>> DB::flushQueryLog(); DB::enableQueryLog(); Company::findAllByNameOrAlias('Frankfurt University of Applied Sciences'); DB::getQueryLog()
=> [
[
"query" => "select * from `companies` where (`name` = ? or exists (select * from `company_aliases` where `companies`.`id` = `company_aliases`.`company_id` and `name` = ?)) and `companies`.`deleted_at` is null",
"bindings" => [
"Frankfurt University of Applied Sciences",
"Frankfurt University of Applied Sciences",
],
"time" => 0.67,
],
]
>>> DB::flushQueryLog(); DB::enableQueryLog(); Company::findAllByNameOrAlias('Frankfurt University of Applied Sciences'); DB::getQueryLog()
=> [
[
"query" => "select * from `companies` where (`name` = ? or exists (select * from `company_aliases` where `companies`.`id` = `company_aliases`.`company_id` and `name` = ?)) and `companies`.`deleted_at` is null",
"bindings" => [
"Frankfurt University of Applied Sciences",
"Frankfurt University of Applied Sciences",
],
"time" => 298.88,
],
]
Notice the query times.
The use case: I am looking for companies by name, but we want to be able to specify aliases that identify the same company. The companies table has an index on name, and the company_aliases table has an index on name, too. company_aliases.company_id has a foreign key constraint referencing companies.id.
Cherry on top: The table company_aliases is empty.
EXPLAIN for the above query:
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|---|---|---|---|---|---|---|---|---|---|
| 1 | PRIMARY | companies | ALL | companies_name_index | NULL | NULL | NULL | 241068 | Using where |
| 2 | MATERIALIZED | company_aliases | ref | company_aliases_company_id_foreign,company_aliases_name_index | company_aliases_name_index | 1022 | const | 1 | Using index condition |
Leaving out the company_aliases table and searching in companies only, gives sub 1 ms run times consistently.
>>> DB::flushQueryLog(); DB::enableQueryLog(); Company::where('name', 'Frankfurt University of Applied Sciences')->first(); DB::getQueryLog()
=> [
[
"query" => "select * from `companies` where `name` = ? and `companies`.`deleted_at` is null limit 1",
"bindings" => [
"Frankfurt University of Applied Sciences",
],
"time" => 0.84,
],
]
>>> DB::flushQueryLog(); DB::enableQueryLog(); Company::where('name', 'Frankfurt University of Applied Sciences')->first(); DB::getQueryLog()
=> [
[
"query" => "select * from `companies` where `name` = ? and `companies`.`deleted_at` is null limit 1",
"bindings" => [
"Frankfurt University of Applied Sciences",
],
"time" => 0.95,
],
]
>>> DB::flushQueryLog(); DB::enableQueryLog(); Company::where('name', 'Frankfurt University of Applied Sciences')->first(); DB::getQueryLog()
=> [
[
"query" => "select * from `companies` where `name` = ? and `companies`.`deleted_at` is null limit 1",
"bindings" => [
"Frankfurt University of Applied Sciences",
],
"time" => 0.83,
],
]
>>> DB::flushQueryLog(); DB::enableQueryLog(); Company::where('name', 'Frankfurt University of Applied Sciences')->first(); DB::getQueryLog()
=> [
[
"query" => "select * from `companies` where `name` = ? and `companies`.`deleted_at` is null limit 1",
"bindings" => [
"Frankfurt University of Applied Sciences",
],
"time" => 0.73,
],
]
>>> DB::flushQueryLog(); DB::enableQueryLog(); Company::where('name', 'Frankfurt University of Applied Sciences')->first(); DB::getQueryLog()
=> [
[
"query" => "select * from `companies` where `name` = ? and `companies`.`deleted_at` is null limit 1",
"bindings" => [
"Frankfurt University of Applied Sciences",
],
"time" => 0.6,
],
]
>>> DB::flushQueryLog(); DB::enableQueryLog(); Company::where('name', 'Frankfurt University of Applied Sciences')->first(); DB::getQueryLog()
=> [
[
"query" => "select * from `companies` where `name` = ? and `companies`.`deleted_at` is null limit 1",
"bindings" => [
"Frankfurt University of Applied Sciences",
],
"time" => 0.9,
],
]
>>> DB::flushQueryLog(); DB::enableQueryLog(); Company::where('name', 'Frankfurt University of Applied Sciences')->first(); DB::getQueryLog()
=> [
[
"query" => "select * from `companies` where `name` = ? and `companies`.`deleted_at` is null limit 1",
"bindings" => [
"Frankfurt University of Applied Sciences",
],
"time" => 0.86,
],
]
>>> DB::flushQueryLog(); DB::enableQueryLog(); Company::where('name', 'Frankfurt University of Applied Sciences')->first(); DB::getQueryLog()
=> [
[
"query" => "select * from `companies` where `name` = ? and `companies`.`deleted_at` is null limit 1",
"bindings" => [
"Frankfurt University of Applied Sciences",
],
"time" => 0.64,
],
]
>>> DB::flushQueryLog(); DB::enableQueryLog(); Company::where('name', 'Frankfurt University of Applied Sciences')->first(); DB::getQueryLog()
=> [
[
"query" => "select * from `companies` where `name` = ? and `companies`.`deleted_at` is null limit 1",
"bindings" => [
"Frankfurt University of Applied Sciences",
],
"time" => 0.53,
],
]
>>> DB::flushQueryLog(); DB::enableQueryLog(); Company::where('name', 'Frankfurt University of Applied Sciences')->first(); DB::getQueryLog()
=> [
[
"query" => "select * from `companies` where `name` = ? and `companies`.`deleted_at` is null limit 1",
"bindings" => [
"Frankfurt University of Applied Sciences",
],
"time" => 0.47,
],
]
>>> DB::flushQueryLog(); DB::enableQueryLog(); Company::where('name', 'Frankfurt University of Applied Sciences')->first(); DB::getQueryLog()
=> [
[
"query" => "select * from `companies` where `name` = ? and `companies`.`deleted_at` is null limit 1",
"bindings" => [
"Frankfurt University of Applied Sciences",
],
"time" => 0.55,
],
]
>>> DB::flushQueryLog(); DB::enableQueryLog(); Company::where('name', 'Frankfurt University of Applied Sciences')->first(); DB::getQueryLog()
=> [
[
"query" => "select * from `companies` where `name` = ? and `companies`.`deleted_at` is null limit 1",
"bindings" => [
"Frankfurt University of Applied Sciences",
],
"time" => 0.53,
],
]
>>> DB::flushQueryLog(); DB::enableQueryLog(); Company::where('name', 'Frankfurt University of Applied Sciences')->first(); DB::getQueryLog()
=> [
[
"query" => "select * from `companies` where `name` = ? and `companies`.`deleted_at` is null limit 1",
"bindings" => [
"Frankfurt University of Applied Sciences",
],
"time" => 0.66,
],
]
>>> DB::flushQueryLog(); DB::enableQueryLog(); Company::where('name', 'Frankfurt University of Applied Sciences')->first(); DB::getQueryLog()
=> [
[
"query" => "select * from `companies` where `name` = ? and `companies`.`deleted_at` is null limit 1",
"bindings" => [
"Frankfurt University of Applied Sciences",
],
"time" => 0.79,
],
]
>>> DB::flushQueryLog(); DB::enableQueryLog(); Company::where('name', 'Frankfurt University of Applied Sciences')->first(); DB::getQueryLog()
=> [
[
"query" => "select * from `companies` where `name` = ? and `companies`.`deleted_at` is null limit 1",
"bindings" => [
"Frankfurt University of Applied Sciences",
],
"time" => 0.84,
],
]
A search in company_aliases is as fast as a search in companies, so it’s a viable workaround to query companies and, if no result was found, query company_aliases. The total query time is still below 2 ms. Essentially, I am just moving the lazy evaluation of the OR into my PHP code. But this shouldn’t be faster than executing the same logic entirely in the DB.
What’s the reason?
The Solutions:
Below are the methods you can try. The first solution is probably the best. Try others if the first one doesn’t work. Senior developers aren’t just copying/pasting – they read the methods carefully & apply them wisely to each case.
Method 1
“A search in company_aliases is as fast as a search in companies, so it’s a viable workaround to query companies and, if no result was found, query company_aliases. The total query time is still below 2 ms. Essentially, I am just moving the lazy evaluation of the OR into my PHP code. But this shouldn’t be faster than executing the same logic entirely in the DB.”
That’s not the same logic. The query is saying give me the rows in companies that either have the same name as this value or can be found in company_aliases with this value. There’s nothing which tells it to stop when it finds a match.
You can expand the OR condition manually:
select *
from `companies`
where `name` = ?
and `companies`.`deleted_at` is null
union all
select *
from `companies`
where exists (select null
from `company_aliases`
where `companies`.`id` = `company_aliases`.`company_id`
and `name` = ?
)
and `companies`.`deleted_at` is null
and ( `name` <> ? or `name` is null)
This should give you a plan which uses an index on companies.name to satisfy the first part, and it should use an index on company_aliases.name followed by a loop to companies based on id for the second part. Assuming those indexes exist, you should be fine.
All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0