> Is that to say that testing many items with an IN doesn't benefit from an index?
Older versions treated IN like a range; newer versions treat IN more like "=".
These are identical:
WHERE elementA = x AND elementB = y
WHERE elementB = y AND elementA = x
These are different:
index(elementA, elementB)
index(elementB, elementA)
Either of those indexes would work equally well for either of those WHERE clause. More discussion and more examples:
http://mysql.rjweb.org/doc.php/index1
> I based that on your earlier comment indicating that one field in the index is better and that maybe v5.6 could handle two fields.
I _think_ that 5.6 does a better job of this than 5.5:
WHERE x IN (...) AND y IN (...)
with INDEX(x,y)
Something about leapfrogging around in the index. Seams like it is called "MRR".
5.6 might even handle
userID IN (y,y,y,y,y) AND startDay > zz
with INDEX(userID, startDay), but possibly not INDEX(startDay, userID)
> I have been in the habit of creating many indices, some with many elements to deal with situations with WHERE cluases
Do EXPLAIN, then look at the "key_len" to try to deduce how many of the fields it actually used. key_len is in bytes. INT is 4 bytes, DATE is 3, DATETIME is 8, etc. NULLable fields are 1 byte longer.
Example:
userID INT NOT NULL,
startDay DATE NOT NULL
INDEX(userID, startDay)
key_len = 4 -- using only userID
key_len = 7 -- using both. (and "Extra" might say 'MRR')
If both are NULL instead of NOT NULL, then the key_lens for those examples would be 5 and 9.
(When I see "5" I jump to the conclusion that it is an INT and it us probably wrongly NULLable.)
A test: Collect several representative SELECTs; build a table (of realistic size); add several different indexes; run EXPLAINs; see which indexes are actually used. Put the useful INDEXes into production.