Dropping suboptimal secondary index not taking effect

1

Hello,

I was looking at an aggregate query, that was taking around 1 sec.

When refreshing the process list I would see a couple of these queries being finished, and new ones coming in.
It was acting ok, but it could be faster.

So I added another secondary multi-column index.

However, this new index was worse. Now queries were taking >15 seconds (before being killed by downstream or completed).

Immediately, I dropped the index.

The DROP INDEX query was successful after 1.3 seconds.

Now I would suspect the aggregate queries were back to taking around 1 second.

Instead, they were piling up.

The queries I could see in process list were running for 15 seconds before being cancelled by downstream (or completed?).

It felt like the new sub-optimal index was not really dropped. It was not shown in the list of indexes though.

I am wondering what happened.

  1. Is it me making false assumptions about what caused the queries to take longer?
  2. Was this just a case of a snowball effect, that once the new sub-optimal index was added the queries started taking longer and piling up, thus when the index was dropped there were still queries in queue? or
  3. Is there something going on behind the scenes when dropping an index, so that it does not take effect right away?

Best regards, Kristian

2

Hey @Kristian,
I would have ran EXPLAIN on the queries after dropping the index to confirm they were not using it. I’ll have to confirm with an internals engineer, but it’s likely that any open transactions would not “see” the index gone until starting a new transaction, and thus a new view snapshot of the data.
Consider using ALTER INDEX … INVISIBLE next time, as this should take immediate effect as it blocks the optimizer’s knowledge of the index. Then you can drop it knowing no trx are using it.

1 Like
3

Thank you, I will run EXPLAIN next time + use INVISIBLE in the future! :slight_smile: Thanks for helping out