USE INDEX throwing not found error while after auto fail-over

Couchbase Server
,
1

I have a 3 node cluster with Couchbase server CE 4.5.1 installed in it for auto-failover. I need to work my application even if one of the node went down. I have a secondary index also created for N1QL. It is created in Server1. When server1 went down, the application will start throwing Index not found error while executing the query.

I know it is because of the unavailability of the index in Server1. Is there any solution? Using the USE INDEX in a clustered environment is an inefficient way?

2

Checkout Duplicate index (9) https://blog.couchbase.com/create-right-index-get-right-performance/
Then list all the duplicate indexes in your use INDEX.

3

Thank you for your reply @vsr1. I have tried it before posting the question. But the query was not executed with the available index. It showed the same index not found error while turned of one node.

My two indexes was like the following. Both created in different nodes.
CREATE INDEX i1 ON travel-sample(LOWER(name),id, icoo) WHERE type = ‘airline’;
CREATE INDEX i2 ON travel-sample(LOWER(name),id, icoo) WHERE type = ‘airline’;

My query was like the following.
select name from travel-sample USE INDEX (i1, i2) where type=“airline”;

4

The query will not use index.

select name 
from  `travel-sample`  USE INDEX ( i1 ,  i2 ) 
where type="airline" and LOWER(name) IS NOT NULL;

At present there is no option. USE INDEX is down it returns error, This will be changed in future releases.

5

@vsr1 sorry i missed it in the query. The query still error.
[
{
“code”: 12016,
“msg”: “Index Not Found - cause: queryport.indexNotFound”
}
]

6

Only option is change USE INDEX remove the index that is down.

7

OK. We have to do the checking in the code level right?
I am using Couchbase Java client SDK. So i am planning to execute the query with first index. If it throw error, then execute same query with second index. Is there any other better way? Is there any method in way to check the existence of index before executing the query.

8

Don’t use USE INDEX hint. Let query decide the index selection.

You can also query SELECT * FROM system:indexes and check what indexes each bucket has and it status

Use EE version with replica.

1 Like
9

If I didn’t use the USE INDEX, I had a few issues as well as performance degradation in my use-case.

Let me describe my use-case with an example, I have two separate indexes with different orders. With respect to the parameter in the request, I have to choose the right index.

Following are the indexes I have:

CREATE INDEX idx_user_atm_1 ON bank-data (user_id,atm_id) WHERE type = ‘transaction_doc’;
CREATE INDEX idx_user_atm_2 ON bank-data (user_id,atm_id) WHERE type = ‘transaction_doc’;
CREATE INDEX idx_atm_1 ON bank-data (atm_id) WHERE type = ‘transaction_doc’;
CREATE INDEX idx_atm_2 ON bank-data (atm_id) WHERE type = ‘transaction_doc’;

I have a transaction list API to get transactions with filter atm_id and user_id. The following are the three use-cases I need to handle.

Case 1. With atm_id filter, the query will be

SELECT user_id, atm_id, date, amount FROM `bank-data` USE INDEX( idx_atm_1 ,idx_atm_2) WHERE atm_id=$atmId AND type = ‘transaction_doc’;

Case 2. With user_id filter, the query will be

SELECT user_id, atm_id, date, amount FROM `bank-data` USE INDEX( idx_user_atm_1 ,idx_user_atm_2) WHERE user_id=$userId AND type = ‘transaction_doc’;

Case 3. With atm_id and user_id filter, the query will be

SELECT user_id, atm_id, date, amount FROM `bank-data` USE INDEX( idx_user_atm_1 ,idx_user_atm_2) WHERE user_id=$userId AND atm_id=$atmId AND type = ‘transaction_doc’;

Case4. Without any filters, the query will be

SELECT user_id, atm_id, date, amount FROM `bank-data` USE INDEX( idx_atm_1 ,idx_atm_2) WHERE type = ‘transaction_doc’;

If I didn’t use the USE INDEX, the problems I faced are:

  1. Case 4 will take both indexes alternatively.
  2. The pagination fails with duplicate entries.
  3. Query taking too much time to execute
10 
CREATE INDEX idx_user_atm_1 ON `bank-data` (user_id,atm_id,date,amount) WHERE type = ‘transaction_doc’;
CREATE INDEX idx_atm_user_1 ON  `bank-data`  (atm_id, user_id,date,amount) WHERE type = ‘transaction_doc’;

CASE 4:

SELECT user_id, atm_id, date, amount
FROM bank-data
WHERE type = ‘transaction_doc’ AND atm_id IS NOT NULL;

Case 4 pagination fails checkout https://blog.couchbase.com/offset-keyset-pagination-n1ql-query-couchbase/

1 Like
11

@vsr1 Thank you so much for your support. Let me try it.

12

@vsr1 I created new indexes as you suggested.

CREATE INDEX idx_user_atm_1 ON `bank-data` (user_id,atm_id,date,amount) WHERE type = ‘transaction_doc’;
CREATE INDEX idx_atm_user_1 ON `bank-data` (atm_id, user_id,date,amount) WHERE type = ‘transaction_doc’;

Now I could remove the USE INDEX. But, when I need to include multiple values for atm_id, the query for Case 1 and Case 3 will change to the following:
Case 1,

SELECT user_id, atm_id, date, amount FROM bank-data WHERE atm_id in [‘atm1’,‘atm2’] AND type = ‘transaction_doc’;

Case3,

SELECT user_id, atm_id, date, amount FROM `bank-data` USE INDEX( idx_user_atm_1 ,idx_user_atm_2) WHERE user_id=‘user1’ AND atm_id in [‘atm1’,‘atm2’] AND type = ‘transaction_doc’;

Do we need any change in index or query? I feel some performance degradation. I have 2 million data in my DB.

13

it should work .check EXPLAIN

CE 4.5.1 is very old. try 6.5.1

14

We are not in a position to upgrade our existing system. Is there any problem in continuing with 4.5.1 version?