Deck order index not useful?

💻 Development
1

@Julius Härtl

We had a report when we upgraded Mariadb that some of the deck indexes in the database were corrupt. As part of that, I looked at the indexes, and one of them is just an index on order in the oc_deck_cards table.

What is the purpose of this index? When do you ever want to process all cards of all decks in order of order (rather than order within stack_id)?

To be any use at all, the index needs to be stack_id, order, it seems to me. Indeed, the table should have a combined (possibly unique?) index on stack_id and order rather than a separate one for each column.

I suspect many of the other indexes may be the same.

2

Did you check what the DB says about this? So, e.g. with MariaDB/MySQL, you can prefix with EXPLAIN to see what indices are used (or not). Unfortunately, I am not aware if there is any query logging possible in NC. Maybe someone can fill in this gap.

Chris

3

Obviously not, because I am not aware what queries the app is building. But it seems obvious to me that it is very unlikely to make a query where this index is useful, and that a combined index would probably speed up the query I imagine it needs to make to display a deck.