Optimizing a MySQL "IN" query
Since the query optimizer decided a full table scan was best, you might need a new query
SELECT Col1, Col2 FROM MyTable WHERE Col3 = 'Val1'
UNION
SELECT Col1, Col2 FROM MyTable WHERE Col3 = 'Val2'
UNION
SELECT Col1, Col2 FROM MyTable WHERE Col3 = 'Val3'
UNION ...

This should force an indexed lookup per value followed by a merge of the query results
UPDATE 2015-12-04 15:35 EDT
Based on jkavalik's comment, I have a suggestion. Please run this
SELECT COUNT(1)/20 INTO @FivePercent FROM RXNCONSO;
SELECT * FROM
(
    SELECT TTY,COUNT(1) ValueCount
    FROM RXNCONSO GROUP BY TTY
) A WHERE ValueCount &gt; @FivePercent
ORDER BY ValueCount DESC;

This will show which values will likely contribute to a table scan.