This is a question for those MySQL (8.2.0) experts out there.

I have a fairly large table (140 million rows) with a composite primary key:
col1 (varchar128)
col2 (int)
col3 (int)
and other non-indexed columns.

When I run a query like this:

SELECT * from myTable where col1 = 'abc' AND col2 = 123 AND col3 = 456

it takes about 21 seconds to complete.
Running an Explain Analyze shows it did a table scan despite all columns being in the PK.
I ran the standard maintenance tasks of optimize and analyze but they had no effect.

I then figured I would try adding individual indexes to each column in addition the the PK.
This resulted in the 1ms query time, the Explain showed it used the new indexes.

I dropped both the new indexes and the existing PK index then recreated the PK index and I had the same behaviour as before, a 21 second query time with explain saying it did a table scan.

I can accept that I need an individual index per column if that is just how MySQL works, in MSSQL I use composite indexes all the time and they work great.
Anyone have any idea why this occurs? I’d rather not have to take the memory hit of adding the additional indexes to all my tables that use composite primary keys.

FYI – I use upsert queries to insert or update data in these tables, those seem to run fine.