What index to build for comparing two string columns against each other

Question

I am trying to run a query like this

select * from email WHERE email_id = thread_id;

How do I create an index that is used for this query that is comparing two of its own columns?

So far I have created two single indexes of email_id and one for thread_id to no avail.
Then I tried a composite index on both:

ADD INDEX `idx_comp` (`email_id` ASC, `thread_id` ASC) VISIBLE

But again, when I examine the query using the 'EXPLAIN' keyword, it doesn't seem to use any index (the possible_keys column remains empty)

How do I create an index for this type of query?

Answer 1

The functional index suggested in the comments above does work, but not with the query form you use.

CREATE INDEX idx ON email ( (email_id = thread_id) );

You can see that if you write the query like you did, it ignores the index and resorts to "ALL" as the join type.

mysql> explain select * from email WHERE email_id = thread_id;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | email | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    4 |   100.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+

This is how to work around that:

mysql> explain select * from email WHERE (email_id = thread_id) = 1;
+----+-------------+-------+------------+------+------------------+------------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys    | key              | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+------------------+------------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | email | NULL       | ref  | functional_index | functional_index | 4       | const |    2 |   100.00 | NULL  |
+----+-------------+-------+------------+------+------------------+------------------+---------+-------+------+----------+-------+

Any index lookup needs to compare to a constant value (or constant range of values). The more common way to do this compares a column to a constant value.

When using a functional index, you can compare an expression to a constant value. But you still need the comparison.

In your example, you assume because your expression already contains a comparison operator, this is enough. But you need to compare that expression to a constant.

You might think that MySQL should abstract this for you, but currently it doesn't implement that kind of optimization.