1

I have a mongo images metadata collection consisting of the following fields: camera_name(str), photographer_name(str), resolution(str), image_size(int in MB, rounded) and timestamp(10 digit UNIX timestamp)

I want to run 2 queries only:

  1. Given camera_name, return records which have timestamp <= 1639457261(sample UNIX timestamp). The records must be sorted in descending order
  2. Given camera_name, photographer_name, resolution, image_size and timestamp, I want to retrieve the records, sorted in the descending order of the timestamp entered.

I created 2 indexes:

  1. { "camera_name": 1, "timestamp": -1 }
  2. { "camera_name": 1, "photographer_name": 1, "resolution": 1, "image_size": 1, "timestamp": -1}

The first index works but when I run the query for the second index, no records are returned. I am sure that there are records present in the collection and I am expecting to get at least 10 records while running the second query but it is returning an empty list.

Is there something wrong the way the index is configured? Thanks

Here is the sample data:

{"camera_name": "Nikon", "photographer_name": "Aaron", "resolution": "1920x1080", "image_size": "3", "timestamp": 1397232415}
{"camera_name": "Nikon", "photographer_name": "Paul", "resolution": "1920x1080", "image_size": "4", "timestamp": 1717286853}
{"camera_name": "Nikon", "photographer_name": "Beth", "resolution": "720x480", "image_size": "1", "timestamp": 1503582086}
{"camera_name": "Nikon", "photographer_name": "Aaron", "resolution": "1920x1080", "image_size": "4", "timestamp": 1500628458}
{"camera_name": "Nikon", "photographer_name": "Paul", "resolution": "1920x1080", "image_size": "6", "timestamp": 1407580951}
{"camera_name": "Canon", "photographer_name": "Beth", "resolution": "1920x1080", "image_size": "5", "timestamp": 1166049453}
{"camera_name": "Canon", "photographer_name": "Paul", "resolution": "720x480", "image_size": "2", "timestamp": 1086317569}
{"camera_name": "Canon", "photographer_name": "Beth", "resolution": "720x480", "image_size": "1", "timestamp": 1400638926}
{"camera_name": "Canon", "photographer_name": "Aaron", "resolution": "720x480", "image_size": "1", "timestamp": 1345248762}
{"camera_name": "Canon", "photographer_name": "Paul", "resolution": "1920x1080", "image_size": "5", "timestamp": 1462360853}
{"camera_name": "Fuji", "photographer_name": "Beth", "resolution": "720x480", "image_size": "2", "timestamp": 1815298047}
{"camera_name": "Fuji", "photographer_name": "Shane", "resolution": "720x480", "image_size": "3", "timestamp": 1666493455}
{"camera_name": "Fuji", "photographer_name": "Beth", "resolution": "1920x1080", "image_size": "5", "timestamp": 1846677247}
{"camera_name": "Fuji", "photographer_name": "Beth", "resolution": "1920x1080", "image_size": "5", "timestamp": 1630996389}
{"camera_name": "Fuji", "photographer_name": "Shane", "resolution": "720x480", "image_size": "2", "timestamp": 1816829362}

The queries I execute:

  1. camera_name=Nikon and timestamp<=1503582086 should return 4 records
  2. camera_name='Fuji' ,photographer_name='Beth', resolution='1920x1080', image_size='5' and timestamp<=1900000000 should return 2 records but I'm getting 0 records
bored_dev_guy
  • 11
  • 2
  • Indexes do not prevent results. Indexes improve speed of queries. If you get no results then either the data is not as expected, or the query is not as expected. This condition has nothing to do with an index specification. – barrypicker Dec 14 '21 at 05:13
  • 1
    Since you have not provided sample data, nor have you provided the query I cannot help you with the results problem. – barrypicker Dec 14 '21 at 05:14
  • @barrypicker I have edited the question and added sample data and the queries and their expected outputs – bored_dev_guy Dec 14 '21 at 05:26
  • Thanks for the sample data. That helps a bit. You have not provided the queries. You have provided what the queries are intended to do. Sometimes the implementation is where the problem resides. I would agree your results do not match your intentions. Please provide the actual queries. – barrypicker Dec 14 '21 at 06:08

1 Answers1

0

Indexes don't "filter" results, they allow you to access data faster based by scanning the index tree instead of scanning raw documents.

This means if the second query "returns nothing", it's unrelated to any index you've built, but the actual query you're using just does not match any documents in the db.

I will also mention that your second index can probably be smaller ( depending on certain assumptions like scale and data distribution ), this can help update/insert performance while additionally reducing storage size. However from the looks of the raw data I would assume these are not urgent considerations of yours.

Tom Slabbaert
  • 21,288
  • 10
  • 30
  • 43