0

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?

2
  • Please test the query execution time after OPTIMIZE TABLE. Does the execution time "jumps" again?
    – Akina
    May 9 at 10:01
  • @Akina It didn't help.
    – Leif
    May 10 at 12:20

1 Answer 1

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.

1
  • Of course you are correct with your analysis of the query behavior. Your query actually is using the index on companies.name. Now I see that it is irrelevant, if companies contains the searched name -- so it does not make sense to use the index on companies.name. Very nice answer!
    – Leif
    May 10 at 12:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.