Taking more time for query execution

Couchbase Server
1

Hi,

I have created sample php application with couchbase server. There are 10,000 documents in my bucket. In my php application has several queries and graphs.it is getting more time to load php application page. i have created only primary index ( CREATE PRIMARY INDEX testPos ON testPos ). I am unable to find the reason for loading time issue. Do i need to create more index for every query? or is any other solution for this issue?
i am using - CB 5.1 and SG 1.5

Thank you.

2

@NaramukAbus can you please explain your use case or your query? You can do an explain to figure out whether you are getting all 10,000 documents are getting a match. If you can explain what you are trying to do and some sample documents then we can figure out best index.

1 Like
3

Thank you raju.
I have written more than 100 queries. I have done pos application. I have sales and product documents. I need to get sales reports(by daily, weekly, monthly and yearly) with item records.

Example Query :
SELECT SUM(c.tActualTotal) AS sale_amount, MILLIS_TO_STR(c.tDateTime,"1111-11-11") as tdate FROM testPos AS c WHERE c.type="sales" AND c.owner="' . $username . '" AND MILLIS_TO_STR(c.tDateTime) BETWEEN "' . $startDate . "T" . $timeFrom . "%" . '" AND "' . $endDate . "T" . $timeTo . "%" . '" GROUP BY MILLIS_TO_STR(c.tDateTime,"1111-11-11"

Thank you.

4

Use following index.

CREATE INDEX ix1 ON testPos(`owner`, MILLIS_TO_STR(tDateTime), tDateTime, tActualTotal ) 
WHERE type="sales";

OR

 CREATE INDEX ix1 ON testPos(`owner`, tDateTime, tActualTotal ) WHERE type="sales";
 SELECT SUM(c.tActualTotal) AS sale_amount, MILLIS_TO_STR(c.tDateTime,"1111-11-11") as tdate
 FROM testPos AS c WHERE c.type="sales" AND c.owner="' . $username . '" AND 
                  c.tDateTime BETWEEN STR_TO_MILLIS("' . $startDate . "T" . $timeFrom . "%" . '")
                              AND  STR_TO_MILLIS("' . $endDate . "T" . $timeTo . "%" . '") 
 GROUP BY MILLIS_TO_STR(c.tDateTime,"1111-11-11");
1 Like
5

Thank you @vsr1 .
Do i want to create index for all queries. because i have more than 100 different queries.

Thanks

6

You ca take look at queries and create index for critical once.
https://blog.couchbase.com/create-right-index-get-right-performance/

1 Like