Support index hints with the QueryBuilder

[merceyz]

Is your feature request related to a problem? Please describe.

The MySQL query optimizer needs a little help choosing the right index every now and then and the query builder doesn't support this so I need to use knex and map the result to entities.

Describe the solution you'd like

A way to provide index hints using the query builder directly.

Describe alternatives you've considered

Using getKnex (or raw queries) and mapping the result to entities.

Additional context

https://dev.mysql.com/doc/refman/8.0/en/index-hints.html
https://stackoverflow.com/a/63603866

[B4nan]

Would a qb.from() method be good for this, or can you propose some API?

[merceyz]

Would a dedicated function make more sense or would that be too MySQL specific? (qb.index)

type IndexHint = {
	type: 'use' | 'force' | 'ignore';
	indexName: string | string[];
	forType?: 'JOIN' | 'ORDER BY' | 'GROUP BY' | Array<'JOIN' | 'ORDER BY' | 'GROUP BY'>;
};

declare function index(hint: IndexHint): void;

declare function index(
	type: IndexHint['type'],
	indexName: IndexHint['indexName'],
	forType?: IndexHint['forType']
): void;

[B4nan]

Well, if this would work only for mysql, then I would rather not have such a common API directly on QB. Apparently sqlite has something similar but with different syntax and postgres does not support it at all.

Maybe we should just have a qb.indexHint(sql: string) that would add to the from clause (after the main table alias). That way we would support sqlite too.

[merceyz]

That would work 👍