MongoDB 2.6.4 query not optimal - takes excessive time to return a result -

we using mongodb 2.6.4 on amazon instance. have query taking excessive amount of time return results. note: using 10gen php driver have extracted raw query purpose of troubleshooting query itself.

@db.instant_leads_properties.aggregate(  [    {       "$match":{          "$or":[             {                "address":{                   "$regex":"louisiana",                   "$options":"i"                 }             },             {                "notes":{                   "$regex":"louisiana",                   "$options":"i"                 }             },             {                "city":"louisiana"              },             {                "state":"louisiana"              },             {                "zip":"71230"              }          ],          "$and":[             {                "featured_expires":{                   "$gt":{                      "sec":1407957033,                      "usec":0                   }                }             }          ]       }    },    {       "$sort":{          "listing_date":-1       }    },    {       "$skip":0    },    {       "$limit":10    } ] ); 

if run query directly on mongodb server takes 20 seconds return nothing.

the specific piece seems causing issue follows. if removed result contains records immediately.

"$and":[    {       "featured_expires":{          "$gt":{             "sec":1407957033,             "usec":0          }       }    } ] 

i have created indexes, compound indexes , have configured amazon ec2 instance using 10gen optimized mongodb ami. neither of these attempts have made difference. our db stats follows:

    {       "serverused": "removed",       "db": "removed",       "collections": 10,       "objects": 4719489,       "avgobjsize": 1222.960180646676,       "datasize": 5.77174712e9,       "storagesize": 6.340694016e9,       "numextents": 49,       "indexes": 39,       "indexsize": 6.506763312e9,       "filesize": 1.4958985216e10,       "nssizemb": 16,       "datafileversion": {         "major": 4,         "minor": 6       },       "extentfreelist": {         "num": 0,         "totalsize": 0       },       "ok": 1.0     }  stats collection being used (instant_leads_properies) follows:  {   "serverused": "removed",   "ns": "staging.instant_leads_properties",   "count": 2204568,   "size": 5.447771456e9,   "avgobjsize": 2471,   "storagesize": 5.877628928e9,   "numextents": 21,   "nindexes": 31,   "lastextentsize": 1532088320,   "paddingfactor": 1.0040000000383855,   "systemflags": 0,   "userflags": 1,   "totalindexsize": 6.4251096e9,   "indexsizes": {     "_id_": 71564528,     "listing_key_1": 120252608,     "state_1_property_type_1_city_1_listing_date_1": 199379936,     "state_1_property_type_1_zip_1_listing_date_1": 184082640,     "state_1_property_type_1_county_1_listing_date_1": 192258640,     "asking_price_1": 103418224,     "living_area_1": 95144112,     "living_area_1_asking_price_1": 117047616,     "listing_date_1_living_area_1_asking_price_1": 137340448,     "zip_1_living_area_1_asking_price_1": 142286928,     "longitude_1_latitude_1": 98978656,     "rem_id_1_created_at_-1": 57837024,     "rem_id_1_listing_date_-1": 54501216,     "point_2dsphere": 37740416,     "property_type_1": 66650752,     "address_1": 78890224,     "notes_1": 861096320,     "city_1": 60543280,     "state_1": 59096128,     "zip_1": 51582384,     "property_type_1_featured_expired_1": 69046320,     "property_type_1_featured_expires_1": 69054496,     "city_1_state_1_zip_1": 100303168,     "address_1_city_1_state_1_zip_1": 144159232,     "address_1_city_1_state_1_zip_1_featured_expires_1": 146423984,     "latitude_1": 112501760,     "notes_text": 2.717898624e9,     "featured_expires_1": 37724064,     "sec_1": 37724064,     "usec_1": 37724064,     "address_1_city_1_state_1_zip_1_featured_expires_1_listing_date_-1": 162857744   },   "ok": 1.0 } 

and list of indexes collection:

{ "v" : 1 , "key" : { "_id" : 1} , "ns" : "staging.instant_leads_properties" , "name" : "_id_"} { "v" : 1 , "key" : { "listing_key" : 1} , "unique" : true , "ns" : "staging.instant_leads_properties" , "background" : true , "name" : "listing_key_1"} { "v" : 1 , "key" : { "state" : 1 , "property_type" : 1 , "city" : 1 , "listing_date" : 1} , "ns" : "staging.instant_leads_properties" , "background" : true , "name" : "state_1_property_type_1_city_1_listing_date_1"} { "v" : 1 , "key" : { "state" : 1 , "property_type" : 1 , "zip" : 1 , "listing_date" : 1} , "ns" : "staging.instant_leads_properties" , "background" : true , "name" : "state_1_property_type_1_zip_1_listing_date_1"} { "v" : 1 , "key" : { "state" : 1 , "property_type" : 1 , "county" : 1 , "listing_date" : 1} , "ns" : "staging.instant_leads_properties" , "background" : true , "name" : "state_1_property_type_1_county_1_listing_date_1"} { "v" : 1 , "key" : { "asking_price" : 1} , "ns" : "staging.instant_leads_properties" , "name" : "asking_price_1" , "background" : true} { "v" : 1 , "key" : { "living_area" : 1} , "ns" : "staging.instant_leads_properties" , "name" : "living_area_1" , "background" : true} { "v" : 1 , "key" : { "living_area" : 1 , "asking_price" : 1} , "ns" : "staging.instant_leads_properties" , "name" : "living_area_1_asking_price_1" , "background" : true} { "v" : 1 , "key" : { "listing_date" : 1 , "living_area" : 1 , "asking_price" : 1} , "ns" : "staging.instant_leads_properties" , "name" : "listing_date_1_living_area_1_asking_price_1" , "background" : true} { "v" : 1 , "key" : { "zip" : 1 , "living_area" : 1 , "asking_price" : 1} , "ns" : "staging.instant_leads_properties" , "name" : "zip_1_living_area_1_asking_price_1" , "background" : true} { "v" : 1 , "key" : { "longitude" : 1.0 , "latitude" : 1.0} , "ns" : "staging.instant_leads_properties" , "name" : "longitude_1_latitude_1"} { "v" : 1 , "key" : { "rem_id" : 1.0 , "created_at" : -1.0} , "ns" : "staging.instant_leads_properties" , "name" : "rem_id_1_created_at_-1"} { "v" : 1 , "key" : { "rem_id" : 1.0 , "listing_date" : -1.0} , "ns" : "staging.instant_leads_properties" , "name" : "rem_id_1_listing_date_-1"} { "v" : 1 , "key" : { "point" : "2dsphere"} , "ns" : "staging.instant_leads_properties" , "name" : "point_2dsphere"} { "v" : 1 , "key" : { "property_type" : 1.0} , "ns" : "staging.instant_leads_properties" , "name" : "property_type_1"} { "v" : 1 , "key" : { "address" : 1.0} , "ns" : "staging.instant_leads_properties" , "name" : "address_1"} { "v" : 1 , "key" : { "notes" : 1.0} , "ns" : "staging.instant_leads_properties" , "name" : "notes_1"} { "v" : 1 , "key" : { "city" : 1.0} , "ns" : "staging.instant_leads_properties" , "name" : "city_1"} { "v" : 1 , "key" : { "state" : 1.0} , "ns" : "staging.instant_leads_properties" , "name" : "state_1"} { "v" : 1 , "key" : { "zip" : 1.0} , "ns" : "staging.instant_leads_properties" , "name" : "zip_1"} { "v" : 1 , "key" : { "property_type" : 1.0 , "featured_expired" : 1.0} , "ns" : "staging.instant_leads_properties" , "name" : "property_type_1_featured_expired_1"} { "v" : 1 , "key" : { "property_type" : 1.0 , "featured_expires" : 1.0} , "ns" : "staging.instant_leads_properties" , "name" : "property_type_1_featured_expires_1"} { "v" : 1 , "key" : { "city" : 1.0 , "state" : 1.0 , "zip" : 1.0} , "name" : "city_1_state_1_zip_1" , "ns" : "staging.instant_leads_properties"} { "v" : 1 , "key" : { "address" : 1.0 , "city" : 1.0 , "state" : 1.0 , "zip" : 1.0} , "name" : "address_1_city_1_state_1_zip_1" , "ns" : "staging.instant_leads_properties"} { "v" : 1 , "key" : { "address" : 1.0 , "city" : 1.0 , "state" : 1.0 , "zip" : 1.0 , "featured_expires" : 1.0} , "name" : "address_1_city_1_state_1_zip_1_featured_expires_1" , "ns" : "staging.instant_leads_properties"} { "v" : 1 , "key" : { "latitude" : 1.0} , "name" : "latitude_1" , "ns" : "staging.instant_leads_properties" , "background" : true} { "v" : 1 , "key" : { "_fts" : "text" , "_ftsx" : 1} , "name" : "notes_text" , "ns" : "staging.instant_leads_properties" , "weights" : { "notes" : 1} , "default_language" : "english" , "language_override" : "language" , "textindexversion" : 2} { "v" : 1 , "key" : { "featured_expires" : 1.0} , "name" : "featured_expires_1" , "ns" : "staging.instant_leads_properties"} { "v" : 1 , "key" : { "sec" : 1.0} , "name" : "sec_1" , "ns" : "staging.instant_leads_properties"} { "v" : 1 , "key" : { "usec" : 1.0} , "name" : "usec_1" , "ns" : "staging.instant_leads_properties"} { "v" : 1 , "key" : { "address" : 1.0 , "city" : 1.0 , "state" : 1.0 , "zip" : 1.0 , "featured_expires" : 1.0 , "listing_date" : -1.0} , "name" : "address_1_city_1_state_1_zip_1_featured_expires_1_listing_date_-1" , "ns" : "staging.instant_leads_properties"} 

please let me know other information provide insight.

thanks jason

my suggestion create text index on address,notes,city , state:

db.instant_lead_properties.ensureindex({address:"text",notes:"text",city:"text", state:"text"}) 

and adjust match part of aggregation follows:

[...]  $match:  {    $text: { $search: "louisiana" },    "featured_expires": { "$gt": { "sec":1407957033,"usec":0 } }  } [...] 

this way, preventing $or , $and chained, experience has impact on performance.

neat features weighted results come that, too.

also, instead of having 2 separate indices sec , usec, compound index 2 fields might help.

without detailed mms monitoring , without knowing exact configuration of servers, there nothing more can see here.