We have collection that’s contains 12k records. We created composite index on this collection from db shell command.
We have java application thats had search api over the above collection, we are building criteria query and executing same using mongo db query template from spring boot framework.
During query execution, we observed that query execution time is time even though proper index in placed at db. We print the query in java app console and try to execute in mongodb compass in query explain section against collection. Here we can observe query execution time 2ms where in search api the same query execution taking 300ms.
Not able to figured out why longer query in java application. I suspect java criteria query not using composite index at created from db level.
As mentioned in the documentation for Aggregation with Java, can you help me if the aggregation from the Java app is making use of the indexes being created.
Having said the above, there could be more reasons of the issue being seen.
There could be network latency issue between the Java Application and the MongoDB server.
or
There could be a large connection pools been created. Could you check the connection pool configuration in your Spring Boot application. Insufficient or misconfigured connection pools can cause delays due to connection establishment overhead.
Finally, to help you diagnose further, could you please help me with some sample code which would help the community to understand the issue in detail.
Thank you for your reply.
I can see in query execution states, i can see index name and documents scanned and key examined.
As you said there could be another issue kind of network latency as i am running my app from local. In actual environment, we see service response time 300ms.
What is optimal connection pool size to be configured?
Can we have multiple indexes on single collection? For different search operation we use different fields?
Having too many indexes , will make write operation costlier correct?