mysql not using index on simple OR condition

Question

I have ran into the age-old problem of MySQL refusing to use an index for seemingly basic stuff. The query in question:

SELECT c.*
FROM app_comments c
LEFT JOIN app_comments reply_c ON c.reply_to = reply_c.id
WHERE (c.external_id = '840774' AND c.external_context = 'deals')
 OR (reply_c.external_id = '840774' AND reply_c.external_context = 'deals')
ORDER BY c.reply_to ASC, c.date ASC

EXPLAIN:

id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   SIMPLE  c   ALL external_context,external_id,idx_app_comments_externals NULL    NULL    NULL    903507  Using filesort
1   SIMPLE  reply_c eq_ref  PRIMARY PRIMARY 4   altero_full.c.reply_to  1   Using where

There are indexes on external_id and external_context separately, and I also tried adding a composite index (idx_app_comments_externals), but that did not help at all.

The query executes in 4-6 seconds in production (>1m records), but removing the OR part of the WHERE condition decreases that to 0.05s (it still uses filesort though). Clearly indexes don't work here, but I have no idea why. Can anyone explain this?

P.S. We're using MariaDB 10.3.18, could that be at fault here?

Answer 1

MySQL (and MariaDB) cannot optimize OR conditions on different columns or tables. Note that in the context of the query plan c and reply_c are considered different tables. These queries are usually optimized "by hand" with UNION statements, which often contain a lot of code duplication. But in your case and with a quite recent version, which supports CTEs (Common Table Expressions) you can avoid most of it:

WITH p AS (
    SELECT *
    FROM app_comments
    WHERE external_id      = '840774'
      AND external_context = 'deals'
)
SELECT * FROM p
UNION DISTINCT
SELECT c.* FROM p JOIN app_comments c ON c.reply_to = p.id
ORDER BY reply_to ASC, date ASC

Good indices for this query would be a composite one on (external_id, external_context) (in any order) and a separate one on (reply_to).

You will though not avoid a "filesort", but that shouldn't be a problem, when the data are filtered to a small set.