What is the ideal composite index for this query?
INDEX(ColB, ColC, ColD)


with the columns in any order, is good.  This takes care of all of the WHERE clause.

Potentially even better is a "covering" index:

INDEX(ColB, ColC, ColD, ColA)


with ColA last.

"Covering means that all the columns in the SELECT are in the index.  That way, the query can be performed in the BTree for the index, and not have to jump over to the data for anything else.

If the table is InnoDB, then the first index will contain ColA.  This is because any secondary index implicitly contains the PRIMARY KEY.

Even in a covering index, the order is important.

Index Merge Intersect is almost never faster than a Composite index that handles the correct columns.  (I think of Index Merge as a clue that you should add a Composite index.)

More on building optimal indexes.

Do not use DESC, it is not as descriptive as SHOW CREATE TABLE !

Drop KEY ColB (ColB) -- it is now redundant.