Mysql – Remove unused Indexes MariaDB

As Nick Chammas pointed out in his comment, your test tables should be combined if they have the same fields and segregated if they have very different fields.

It is difficult to imagine 100,000 different test tables. If this is not complete hyperbole then you might need to consider other options.

One option would be a document-oriented database. This would be efficient for storage of loosely structured data and easy retrieval of data for a particular patient. It would be pretty useless for rapid retrieval of data for similar tests across multiple patients.

Another option would be Entity-Attribute-Value (EAV). EAV is considered an anti-pattern by most people, but yours may be a situation where it could be useful. EAV would be less efficient at retrieving the results of a particular test for a particular patient, but it would certainly be better at getting all results for a particular patient than joining thousands of different test tables. It would also be better than other approaches for getting the same test result values across multiple patients.

In this case, yes, they appear to be redundant. The second index will satisfy any queries that would benefit from the first one. Also, both indexes appear to contain the same columns, so rows within the index leaf pages should be about the same size in either. Your non-leaf index pages will be a bit larger with more key columns, but that probably won't amount to a significant size increase, unless the index rows are quite large and only fit a few per page.

It's important to consider the size of the index rows when deciding if an index is truly redundant. If you have an index on a couple of int columns, a decimal, and a datetime, the rows will be much smaller than if you have another index on those same columns plus a character column that averages a couple hundred bytes. Any analytical queries that would aggregate a large number of rows would benefit from the index that has rows much more densely packed on the data pages, even though either index could technically satisfy the query.

So, make sure you consider the difference in average row size, and whether or not that has any performance implications before you go dropping any redundant-looking indexes.