I have used several databases (relational + NoSQL) as a developer for 3+ years but I have a basic idea about the core of the database processes and database administration tasks. My question is about index selection problem. What I understood when reading through several articles is that in some databases query optimizer can choose the most relevant index(es) and in some other a database administrator have the authority to select the index(es) from the suggested list of indexes by the optimizer. But the idea on the process of selecting indexes is still vague. Can you give me a descriptive answer on how the index selection process happens or recommend me a book or a article to read to get a precise idea on the process of index selection from A to Z. The key areas I need information are,
- What are the criteria used to decide an index is the most appropriate for a query?
- Is there a difference between index selection in relational databases and index selection in NoSQL databases?
- What role does the query optimizer plays in index selection?
- If you are to automate the index selection process what would you most consider on giving solutions or taking new approaches?
- Are there any practical problems when in it comes to index selection and the performance of the database?
- Do I have the freedom of choosing different index structures (b tree, b+ tree, hashing,…) while creating indexes initially or do I need to stick in to one type of index structure?