Understanding Compound, Multikey, and Geospatial Indexes in NoSQL Quiz

Explore essential concepts of indexing in NoSQL databases, focusing on compound, multikey, and geospatial indexes. This quiz helps clarify how different index types work, their purposes, and scenarios where each is most effective.

1. Purpose of Compound Indexes

   Which type of index allows the database to efficiently query documents based on multiple fields, such as both 'city' and 'age' in a single search?

   1. Hashed index
   2. Compound index
   3. Geospatial index
   4. Text index

   Explanation: A compound index indexes multiple fields in a single structure, allowing efficient queries that involve those fields together. Geospatial and hashed indexes focus on spatial data or hash values, and text indexes are optimized for text search. Only compound indexes provide efficiency for multi-field queries.

2. Multikey Index and Arrays

   If a document in a NoSQL database has a field containing an array of values, which index type is required for efficient querying?

   1. Single field index
   2. Multikey index
   3. Geospatial index
   4. Wildcard index

   Explanation: A multikey index is designed to index array values so each element is indexed separately, supporting efficient queries for arrays. Single field indexes only apply to non-array fields, geospatial indexes are not suited for general arrays, and wildcards serve different purposes.

3. Function of Geospatial Indexes

   Which type of index would you use to find all places within a 5-mile radius of a given coordinate in a NoSQL database?

   1. Compound index
   2. Sparse index
   3. Geospatial index
   4. Text index

   Explanation: Geospatial indexes efficiently support queries involving locations, distances, and coordinates. Compound indexes and text indexes do not handle spatial data, while sparse indexes are designed for handling missing data and are not specific to location searches.

4. Structure of Compound Index

   In a document where 'category' is indexed before 'price' in a compound index, which query will make the best use of this index?

   1. Query filtering by 'description' and 'price'
   2. Query filtering by both 'category' and 'price'
   3. Query filtering only by 'description'
   4. Query filtering only by 'price'

   Explanation: Compound indexes are most effective when queries include the leading field ('category') and any subsequent indexed fields ('price'). Filtering only by the second field or an unrelated field will not utilize the index fully. Including both fields in the query is needed for optimal use.

5. Multikey Index Limitation

   What happens if you attempt to create a compound index on two array fields in a NoSQL collection?

   1. The database ignores duplicate values
   2. Both arrays are merged automatically
   3. The index creation is not allowed
   4. Only one array field is indexed

   Explanation: NoSQL databases generally do not allow compound indexes on multiple array fields, as this can create too many index entries and lead to performance issues. The arrays are not merged, indexed singly, or deduplicated automatically. Instead, index creation fails for this configuration.

6. 2dsphere vs. 2d Index

   Which index type should you use to store and query spherical geometry data, such as latitude and longitude coordinates?

   1. 2dsphere index
   2. 2d index
   3. Text index
   4. Hashed index

   Explanation: 2dsphere indexes are specifically designed for storing and querying data on a spherical surface, making them ideal for latitude and longitude. 2d indexes work for flat coordinate systems, not spheres. Text and hashed indexes are irrelevant to geometry data.

7. Index Option for Nested Arrays

   Which index type is appropriate when you want to index individual items within a nested array field, such as 'tags': [['red', 'blue'], ['green']]?

   1. Single field index
   2. Multikey index
   3. Geohash index
   4. Sparse index

   Explanation: Multikey indexes index every element in each sub-array, making them well suited for nested arrays. Single field indexes are not designed for arrays, and geohash indexes are only for location data. Sparse indexes help with null values but not array elements.

8. Querying with Geospatial Indexes

   When using a geospatial index, which of the following queries is supported?

   1. Filtering stores by opening hours
   2. Finding all stores within a 10 kilometer circle
   3. Retrieving stores with the highest customer ratings
   4. Sorting stores based on alphabetical order

   Explanation: Geospatial indexes support queries based on location, such as proximity searches within a specified distance. Sorting alphabetically, filtering by store hours, and finding highest ratings are not location-based and do not benefit from a geospatial index.

9. Effect of Compound Index Field Order

   Why does the order of fields matter in a compound index such as { state: 1, city: 1 }?

   1. It determines which documents are returned
   2. The order changes the datatype of the fields
   3. The index is most efficient when queries include the first field
   4. It prevents duplicate values in queries

   Explanation: With compound indexes, queries that include the leading field take full advantage of the index structure. The order does not affect which documents are returned, prevent duplicates, or change datatypes. Field order only affects index usage efficiency.

10. Unique Constraint with Compound Indexes

    When creating a unique compound index on 'email' and 'username', what happens if a document with an existing combination of both tries to get inserted?

    1. Only the 'username' is checked for uniqueness
    2. The insert succeeds and the index updates automatically
    3. Both existing and new documents are merged
    4. The insert is rejected due to the unique violation

    Explanation: A unique compound index prevents duplicate combinations of the specified fields, so an insert with an existing combination is rejected. It does not check only one field, merge documents, or allow duplicate entries with automatic updates.