0

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:

enter image description here

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%'
 ) )

enter image description here

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):

enter image description here

8
  • Have you tried a join?
    – mustaccio
    Nov 6, 2021 at 17:46
  • @mustaccio yes I have tried to use a join and a tuplie index (id, name) on the company, but the query ended up beeing 400ms, where if I the there WHERE IN, the whole query reduces to 2ms. However, I have to do 2 seperate queries, now I wonder if I can do it in one.
    – Adam
    Nov 6, 2021 at 17:51
  • Have you tried using a Refactored Subquery? (CTE, WITH clause) Nov 6, 2021 at 18:32
  • @MichaelKutz - "subquery refactoring" is Oracle-speak - everyone else calls them CTEs! :-)
    – Vérace
    Nov 6, 2021 at 18:43
  • @mustaccio I edited my join approach to the question - as you can see it uses no indexes on the users table thats probably why its so slow
    – Adam
    Nov 6, 2021 at 21:29

1 Answer 1

1

Classic solution: OR --> UNION:

( SELECT * FROM users WHERE first_name LIKE 'bill%' )
UNION DISTINCT
( SELECT * FROM users WHERE last_name LIKE 'bill%' )
UNION DISTINCT
( SELECT * FROM users WHERE company_id in (100) )

with a [potentially] different index designed for each SELECT:

INDEX(first_name),
INDEX(last_name),
INDEX(company_id)

None of these are useful for performance: JOIN, CTE, WITH, LEFT JOIN, subquery, derived table

2
  • I think whenever I have an and condition, JOIN works well for me in terms of performance. I guess the last sentence of your post is only in the scenario of or condition's containing both tables?
    – Adam
    Nov 7, 2021 at 6:20
  • 1
    @Adam - AND can be optimized in many situations, especially when the ANDs refer to a single table. When AND is used across two tables (and a JOIN), the Optimizer will decide which table to start with, it might be able to use the conditions in both table lookups. It depends. OR is rarely optimizable. (Looking at specific examples may be instructive.) See also: mysql.rjweb.org/doc.php/index_cookbook_mysql
    – Rick James
    Nov 7, 2021 at 15:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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