What really happen when update and insert data to table with secondary index on regular column

General
1

Hi there,
I have a table like below:

CREATE TABLE test ( id text, status int, "desc" text, PRIMARY KEY(id) ) WITH transactions = { 'enabled' : true };
CREATE INDEX test_status ON test (status) INCLUDE ("desc");

When execute insert like below:

Insert Into test(id,status,"desc") values('id',1,'xxx');
Insert Into test(id,status,"desc") values('id',2,'xxx');

Will the below cql would be really executed?

Insert Into test(id,status,"desc") values('id',1,'xxx'); 
Insert Into test_status(status,id,"desc") values(1,'id','xxx');
Insert Into test(id,status,"desc") values('id',2,'xxx'); 
Insert Into test_status(status,id,"desc") values(2,'id','xxx');

This will lead to one row in test but two rows in test_status

I ask this because I have a similar table like this,
and encounter Server Error DeadLine when execute :

select * from test where status=1 limit 2;

However there’s only 3700 rows: select count(*) from test;

So use update clause could fix this case, right?

update test set id='id' and status=1 and "desc" ='xxx';
update test set id='id' and status=2 and "desc" ='xxx';

Thanks

2

When the table has an index, the INSERT will read the row, check if any indexes will be modified, and also update the index. On test_status index the changes will be:

Insert Into test_status(status,id,desc) values(1,'id','xxx');
DELETE FROM test_status(status,id) VALUES (1,'id');
Insert Into test(id,status,"desc") values(2, 'id','xxx');

Only 1 row will be exposed to the query. The DELETE is actually an INSERT in LSM databases and will be removed physically on compaction. While 1 row will be exposed, it may read 3 rows in the database (1st insert, then delete, then insert) depending on the CLUSTERING order by (default ASC).

This is a timeout. Were you running a lot of INSERTS in the table ? This would result in many tombstones that the query would have to scan a lot of data until it finds the required row.

3

Hi @dorian_yugabyte,

I’m trying to reproduce this the two days, but still not.
Will try your suggesstion(compact) if it happen again.

Thank you

4

If it happens again, please also check yb-tserver .INFO & .WARNING logs before compacting the table and share them here.