I have below bucket in Couchbase I am using inner join to fetch the document
We have global secondary index for parent and child document.
- Total records in bucket : 4,300,000
- Query Execution : 5-7 sec
- RAM Used is 32 GB (allocated 90 GB)
- Disk Used is 20 GB
Its like we have parent document and making the several copy of child document with parent document
Example:
{
"document_id" : "p-001",
"document_desc" : "parent doc 1",
"document_type" : "parent"
},
{
"document_id" : "c-001",
"parent_document_id" : "p-001",
"document_desc" : "child doc of parent 1",
"document_type" : "child",
"document_status" : "NEW",
"customer_id" : "c-001"
},
{
"document_id" : "c-002",
"parent_document_id" : "p-001",
"document_desc" : "child doc of parent 1",
"document_type" : "child",
"document_status" : "NEW",
"customer_id" : "c-001"
}
I am using inner join to fetch the document
SELECT * FROM
test_document AS parent
JOIN
test_document AS child
ON child.parent_document_id = parent.document_id
WHERE child.customer_id = 'c-001'
AND child.document_status IN ('NEW', 'OLD', 'OLDER')
Can I improve this query to make the query execution within few milliseconds (before 1 second)? Or any other thoughts to achieve query execution before 1 seconds?