0

I have a MySQL table which I'm trying to search a pair of columns for a single value. It's quite a large table, so I want the search time as fast as possible.

I have simplified the tables below for ease of understanding

SELECT * FROM clients WHERE name=? OR sirname=?

VS

SELECT * FROM clients WHERE ? IN (name, sirname)

with indexes on name and sirname

EXPLAIN on the former uses the indexes, but not on the latter

Is this accurate, or is there some optimisation going on under the hood which EXPLAIN doesn't catch?

Strongly related to Checking multiple columns for one value, but cannot discuss there due to age of thread.

KilleR
  • 71
  • 1
  • 7

1 Answers1

0

Because MySQL generally uses one index per table reference in a query, you will probably have to do it this way:

SELECT * FROM clients WHERE name=?
UNION
SELECT * FROM clients WHERE sirname=?

This will count as two table references for purposes of index selection. The appropriate index will be used in each case.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • The first query DOES use the indexes, MySQL handles multiple indexes just fine. – KilleR Dec 18 '17 at 17:11
  • 1
    @KilleR - Check the `EXPLAIN`; "Index merge" is a _kludge_ to work around the lack of any useful index. Bill's solution can use two separate indexes much more efficiently. – Rick James Dec 23 '17 at 20:32