Secondary Index doesn't work in Update and Delete statements in spanner. Due to which sometimes simple update and delete statements take a lot of time
I tried this: "update table1@{FORCE_INDEX=i_index1} set column1='abc' where column2='xyz'"
If this doesn't work then what is the alternate to make update and delete statements fast
Most of the time, Cloud Spanner will automatically choose indexes that would make a query most efficient and there would be no need to specify any. In some cases, an index could choose an index that would make a query slower; which could be a possible explanation as to why your statements are running for a long time.
It is possible to troubleshoot slow queries by looking at their secondary indexes. If you do find out that an index is slowing down your query, then you can specify another index.
As far as your Cloud Spanner goes, secondary indexes are used for common queries, so only SELECT statements, to speed them up; indicating that UPDATE and DELETE statements will not include a secondary index.
Since you can only use your secondary index in a SELECT statement, I suggest that you try the following: UPDATE table1 SET column1 = ’abc’ WHERE columnX IN (SELECT columnX FROM table1@{FORCE_INDEX = i_index1} WHERE column2 = ’xyz’)
You basically retrieve column values and set them as you WHERE condition values to match, using the secondary index as an intermediary. You might need to change the statement a bit if you want to match many columns.