I have a users table with a single index on each of the following columns first_name
, last_name
and company_id
.
Running this query.
SELECT
*
FROM
`users`
WHERE
(`first_name` LIKE 'bill%'
OR `last_name` LIKE 'bill%'
OR company_id in (100))
uses all indexes as expected:
However, running this modified query, does not use any of the indexes:
SELECT
*
FROM
`users`
WHERE
(`first_name` LIKE 'bill%'
OR `last_name` LIKE 'bill%' Or company_id in (
SELECT
`id`
FROM
`companies`
WHERE
`name` LIKE 'bill%'
) )
Instead, it iterates over all user records and for each it executed the sub-query.
This makes me wonder, is it possible to tell SQL to execute the subquery
SELECT
`id`
FROM
`companies`
WHERE
`name` LIKE 'bill%'
only once independently and not for each single query again?
Edit: This is with JOIN:
SELECT
*
FROM
`users`
INNER JOIN
`companies` ON `companies`.`id` = `users`.`company_id`
WHERE
(`first_name` LIKE 'bill%'
OR `last_name` LIKE 'bill%'
OR `companies`.`name` LIKE 'bill%')
Its much slower and no index is used on users table, also I have indexed (id,name) on companies
and in addition (company_id, first_name) aswell as (comapny_id,last_name):
WITH
clause)