date range queries optimization
Jan Sørensen
Is there any trick in optimizing date range queries ?
I have the following situation:
Initially the dates were persisted in mongo date objects ( until I got problems with 1/1/1970 as described in here: http://groups.google.com/group/mongodb-user/browse_thread/thread/1261b1fec789fd51/653f9b665d6dd67f?show_docid=653f9b665d6dd67f ).
Since than I tried almost everything that came in my mind.
First mongo was upgraded to 1.6.2 and the php extension to 1.0.8.
On second phase I tried persisting dates as timestamps but this was slower than using date objects – both tests had their indexes and were utilized correctly as explain shows. Querying wide range again resulted in either 0 results or in wrong number of results (which I suppose is due to: http://jira.mongodb.org/browse/SERVER-1285).
In addition during the tests I noticed that using $in doesn't filter correctly (in timestamps), while $lte/$gte is fine.
Spitting the query in two (in order to overcome 1/1/1970 in the range) resulted in execution in more than minute, while mysql tuned with correct indexes displays data in ~6 sec.
The result set is always limited to 10, only the needed columns are selected (not all), index is not corrupted and is utilized, what else could be wrong ?
Regards,
js
---
Kristina Chodorow
1) the explain of your query
2) getIndexes() for the collection.
3) mongostat output for before/during/after the query?
--
You received this message because you are subscribed to the Google Groups "mongodb-user" group.
To post to this group, send email to mongod...@googlegroups.com.
To unsubscribe from this group, send email to mongodb-user...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/mongodb-user?hl=en.