MongoDB

Question 1

For a movie collection, increase the performance of a query that returns movies released in the last 5 years with their names in sorted order.
Is it the right way?
db.movie.createIndex({released_date: -1}).sort({name: 1})

Answer 1

db.movie.createIndex({released_date: -1, name: 1});
No.
Sorting w/o indexes will be very costly as it will fetch docs from the disk and sort them in RAM. The ideal way is to use indexes not only for search but also for sorting.

Question 2

How do you speed up specific queries in MongoDB and in Cassandra?

Answer 2

In MongoDB, create the right indexes.
In Cassandra, choose the right Primary Key = Partitioning Key + Clustering Columns.

Question 3

For a movie collection, increase the performance of a query that returns all “romantic” movies.
Note: tags: [“violence”, “romantic”, “family”]

Answer 3

db.movie.createIndex({tag: 1})
It creates a MultiKey index on the tag key as its array field.

Question 4

For an inventory collection, increase the performance of the query that returns all devices that has one of the WAN port down.
Note: Each device has two WAN ports i.e wan_ports : [{}, {}]

Answer 4

db.inventory.createIndex({wan_ports.status : 1})

Create an index on KEYS of embedded docs but not on docs itself.
db.inventory.createIndex({wan_ports : 1}). Bad Idea.

Question 5

Create a user collection where each user has a unique key called “user_id”?

Answer 5

db.user.createIndex({user_id : 1}, {unique: true})
In MongoDB, uniqueness is enforced by a unique index.

Question 6

In company collection, optimize query (while taking care of index size) to list company names with IPO.

Answer 6

db.company.createIndex({name: 1}, {partialFilterExpression: { ipo: {$exists: true}}})
Partial Index preferred, as multiple filter option available, unlike sparse indexes which provide only exists option.

Question 7

A collection has compound index {a: 1, b: 1, c: 1} and the query is {c: 10, a: 5, b: 23}. Will the query use the index?

Answer 7

Yes.
Mongo is smart enough to read query as { a: 5, b: 23, c: 10} and hence use the index

Question 8

A collection has compound index {a: 1, b: 1, c: 1} and the query is {c: 10, b: 23}. Will the query use the index?

Answer 8

No

Question 9

A collection has compound index {a: 1, b: 1, c: 1} and the query is {a: 10}. Will the query use the index?

Answer 9

Yes. This is an index prefix query
Index Prefixes {a: 1}, {a: 1, b: 1}

Question 10

In a big cluster, you need to see how a query will run without running it. How to do that?

Answer 10

db.collection.query.explain()

Question 11

In a big cluster, you need to see how a query executes. How to do that?

Answer 11

db.collection.query.explain(“executionStats”)

Question 12

For this query, provide various options for creating a compound index.
Query: { college = “IITD”, score > 75%, Sort on student_name }

Answer 12

{college: 1, score: 1, student_name: 1} works well in general but if the number of records to sort is too large then the sorting will happen in memory causing a spike.

Alternate Tradeoff: {college: 1, student_name: 1, score: 1}
It will read more keys from Index but there is no need for sorting in RAM as the index will provide already sorted data.
ESR: Equality, Sort, Range

Question 13

How to create a circular ring buffer in MongoDB?

Answer 13

Using Capped Collections

Question 14

How to log the queries on a specific database?

Answer 14

Database profiler
> use dbName
> db.setProfilingLevel(2) # logs all operations
> db.setProfilingLevel(0) # disables profiler

profiler logs into the system.profile collection
> db.system.profile.find({})

Question 15

How do you clean up the database profiler logs?

Answer 15

db.setProfilingLevel(0);db.system.profile.drop();db.setProfilingLevel(2);

Question 16

When reading docs with projection, does it escape reading data from the disk? Is projection server side or client side?

Answer 16

The document is read fully even with projections.
Projection is done on the server side and hence client sees only meaningful traffic.

Question 17

What are routed queries? What is the other type?

Answer 17

Queries that will fetch data from the single shard.
Scatter and Gather.