Using index in Update Clause on Apache Ignite Sql Query

Question

How do I choose a Index to use on an UPDATE Clause like this. I need to specify the index of the columns used in WHERE clause?

UPDATE
    DB.MY_TABLE
SET
    BLOCKED = true,
    HOME = '1',
    WORK = '2',
WHERE
    NAME = 'Me';

The column 'NAME' is indexed with INDEX_NAME but the other columns are also indexed with other indexes. I wanted to do somenthing like that to specify which index I am using (Which seems to work just on SELECT clauses):

SELECT
    *
FROM
    DB.MY_TABLE
USE INDEX(INDEX_NAME)
WHERE
    NAME = 'Me';

I don't think there's a way to do it. Having said that, I'm surprised it would pick the wrong index. — Stephen Darlington, Mar 04 '22 at 14:51
The problem is that we don't know for sure what index is being used, since the "explain" does not work for UPDATE clauses, just for SELECTs. — Luiz Henrique, Mar 04 '22 at 17:21

score 1 · Accepted Answer · answered Apr 11 '22 at 22:12

How Ignite executes these queries - at the time of writing this post - is by spliting the query into two parts:

SELECT with the same condition as specified in the original query.
Iterate over the SELECT results and update each record as specified in the SET clause.

It's usually easy to guess how the SELECT part will look like based on the original query. In your case, I'm pretty sure SELECT * FROM DB.MY_TABLE WHERE Name = 'Me' is the query that will be executed.

I would just check that EXPLAIN SELECT * FROM DB.MY_TABLE WHERE Name = 'Me' uses the index you want it to use and then trust the system to do the UPDATE correctly.

Using index in Update Clause on Apache Ignite Sql Query

1 Answers1