Search code examples
sqlindexingignite

Using index in Update Clause on Apache Ignite Sql Query

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';
Solution

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

    1. SELECT with the same condition as specified in the original query.
    2. 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.