Index Proposals

Posted on

Index Proposals – Managing your servers can streamline the performance of your team by allowing them to complete complex tasks faster. Plus, it can enable them to detect problems early on before they get out of hand and compromise your business. As a result, the risk of experiencing operational setbacks is drastically lower.

But the only way to make the most of your server management is to perform it correctly. And to help you do so, this article will share nine tips on improving your server management and fix some problem about linux, mysql, sql-server, database, database-administration.

As a theoretical database question, if you are asked ‘which indexes would you propose and why?’ are the answers from primary, clustering and secondary index? How would we know if we should introduce one of those vs a B-Tree?

This should be from a simplistic indexing perspective, as I am asking due to course material related to indexes, where I have covered some introductory material.

Solution :

primary and secondary indices are orthogonal to b-tree and hash indices, and both of these are orthogonal to clustered and non-clustered indices.

  • The first class of indices are logical indices. They are high-level indicators about the structure of your data. The primary key should uniquely identify your data, which means it should be one of what relation database theory calls a “candidate key”. Secondary indices are for speeding up queries against other fields than the main index and they do not need to be candidate keys.

  • The second class are specific index implementations, fit for slightly different queries. b-tree can fulfill range queries (WHERE c > 3 AND c < 7) which hash cannot. hash is however O(1) (constant time) in the average case on exact queries (WHERE c = 5), while b-tree is O(lg n) (logarithmic time) always. b-tree and hash are both well-defined datastructures, but they have very little to do with your actual data.

  • The third class defines if the entire table is sorted on disk by the sorting order of the index. If that’s the case it’s called a clustered index.

As you can see an index can be a primary clustered b-tree or a primary clustered hash or some other combination drawn from these three different classes.

If you were asked which indices to introduce, the answer would foremost be from the first class. The question is probably about the schema. That’s your starting point at least. Only when you have decided what indices to introduce do you need to think about whether they should be b-trees, clustered etc. The first class is a logical one, tightly coupled with how you are using the data. The others are implementation details.

Leave a Reply

Your email address will not be published. Required fields are marked *