Index hints

[sushantdhiman]

Related support information

• MySQL https://dev.mysql.com/doc/refman/8.0/en/index-hints.html
• MariaDB
• MSSQL https://docs.microsoft.com/en-us/sql/t-sql/queries/hints-transact-sql-query?view=sql-server-2017
• SQLite https://www.sqlite.org/lang_indexedby.html

Pull requests #9378 , #9409, #8401, #11573

[jessethomson]

Okay, I think a good starting point might be to go over SQL capabilities, and how/if we want to implement all of these.

Valid MySQL queries:

1. SELECT * FROM t1 USE INDEX (); Without using indices
2. SELECT * FROM t1 USE INDEX (i1); With single index
3. SELECT * FROM t1 USE INDEX (i1,i2); With multiple indices
4. SELECT * FROM t1 USE INDEX (i1) USE INDEX (i2); With multiple USE INDEX

I think 1, 2, and 3 are all reasonable functionality. I don't see any reason why we would need to implement 4. As far as I know, 4 is simply a syntactically different way to represent 3, without adding any new functionality (I could be wrong).

Note: It is perfectly valid to use the same index more than once in an index hint.
For example:
SELECT * FROM t1 USE INDEX (i1) USE INDEX (i1);
and
SELECT * FROM t1 USE INDEX (i1,i1);
are both valid statement so we don't need to enforce index uniqueness.

[jessethomson]

Also, it looks like all 3 Pull Requests referenced in the original post are for MySQL. I don't think there is any actual demand for this feature outside of the MySQL dialect; at least nothing that I have been able to find online.

[harbolaez]

Agree with you @jwt1143 on my PR I was able to pass a collection of indexes. I like option 1, 2 and 3 aswell

SELECT * FROM Order USE INDEX(idx_status_id) WHERE status_id = 1

[harbolaez]

Also, I like to support the ability to use FORCE INDEX sometimes is useful but rare.

[ezequielzacca]

is not rare at all for very large databases

[jessethomson]

Okay. I think it makes sense to support force (and probably ignore too in that case).

Here's an API proposal:

We add an indexHints property to the sequelize options.

db.User.findOne({
    where: {
        email: "joe@example.com",
    },
    indexHints: [
        { type: "use", values: ["i1"] }
    ],
});

Examples:

SELECT * FROM t1 USE INDEX ();

indexHints: [
    { type: "use", values: [] },
]

SELECT * FROM t1 USE INDEX (i1);

indexHints: [
    { type: "use", values: ["i1"] },
]

SELECT * FROM t1 USE INDEX (i1,i2);

indexHints: [
    { type: "use", values: ["i1", "i2"] },
]

SELECT * FROM t1 FORCE INDEX (i1);

indexHints: [
    { type: "force", values: ["i1"] },
]

SELECT * FROM t1 IGNORE INDEX (i1);

indexHints: [
    { type: "force", values: ["i1"] },
]

SELECT * FROM t1 USE INDEX (i1,i2) IGNORE INDEX (i2);

indexHints: [
    { type: "use", values: ["i1","i2"] },
    { type: "ignore", values: ["i2"] },
]

Notes

I prefer using objects over arrays to represent each index hint because they are more explicit.
For example:

indexHints:[
    ["use", ["i1","i2"]]
]

is not nearly as clear as

indexHints: [
    { type: "use", values: ["i1","i2"] },
]

Being more verbose shouldn't be an issue as indexHints are only used in limited/rare circumstances.

[sushantdhiman]

@jwt1143 Your proposal looks good, some comments

type should be an constant like query hints

indexHints: [
    { type: IndexHints.USE || FORCE || IGNORE, values: ["i1","i2"] },
]

I also do perfer object based approach

indexHints: [
    { type: IndexHints.USE, values: ["i1","i2"] },
]

As for implementation

MSSQL

We are only going to introduce this

WITH (TableHints, INDEX (<index>))

Form of table hint expansion, we already support table hints for MSSQL, we just need to include index part when indexHints is also supplied, see this pull request

SQLite

Only need to add

INDEXED BY <index_name>

For any given indexHints

MySQL

Up to you, as that is your primary objective with this issue

[benjamin658]

Will the indexHints option support mariadb dialect?

[papb]

@benjamin658 Thanks for adding it on #11573 :)

[timbowhite]

MySQL supports index hints on JOIN statements. It'd be nice to be able to do this:

Project.findAll({
  where: {
    id: {
      [Op.gt]: 623
    },
    name: {
      [Op.like]: 'Foo %'
    }
  },
  include:[{
    association: Company,
    indexHints: [
      {
        type: IndexHints.USE, 
        values: ['index1', 'index2'] 
      }
    ]
  }]
})

and get something like this:

SELECT * FROM Project
INNER JOIN `company` AS `Company` 
  USE INDEX (`index1`,`index2`) 
  ON `Project`.`company_id` = `Company`.`id`
WHERE name LIKE 'FOO %' AND id > 623;

PR incoming.

[theironcook]

Is it possible to get indexHints enabled with sql server / tedious?

[QuanticPotatoes]

@timbowhite The index hints on JOIN statements features would be so life saving 🙏

[arafat-al-mahmud]

Any workaround in v3?