How do I specify a MySQL index hint?

Hi, I am trying to build the following query (as a CTE, as part of a larger query), but I can not work out how I can pass the index hint (

USE INDEX ...

USE INDEX ...

USE INDEX ...

USE INDEX ...):

  SELECT 
    sc.synopsis_fk,
    'characters',
    JSON_ARRAYAGG(c.name)
  FROM synopsis_characters sc
  USE INDEX (idx_character_order)
  INNER JOIN characters c         
          ON sc.character_fk = c.id 
  GROUP BY 
    sc.synopsis_fk

  SELECT 
    sc.synopsis_fk,
    'characters',
    JSON_ARRAYAGG(c.name)
  FROM synopsis_characters sc
  USE INDEX (idx_character_order)
  INNER JOIN characters c         
          ON sc.character_fk = c.id 
  GROUP BY 
    sc.synopsis_fk

  SELECT 
    sc.synopsis_fk,
    'characters',
    JSON_ARRAYAGG(c.name)
  FROM synopsis_characters sc
  USE INDEX (idx_character_order)
  INNER JOIN characters c         
          ON sc.character_fk = c.id 
  GROUP BY 
    sc.synopsis_fk

  SELECT 
    sc.synopsis_fk,
    'characters',
    JSON_ARRAYAGG(c.name)
  FROM synopsis_characters sc
  USE INDEX (idx_character_order)
  INNER JOIN characters c         
          ON sc.character_fk = c.id 
  GROUP BY 
    sc.synopsis_fk

I tried using raw sql:

    .with(
      'chars',
      db => db
        .selectFrom(sql`synopsis_characters USE INDEX idx_character_order`).as('sc')
        .select([
          ...
        ])
    )

    .with(
      'chars',
      db => db
        .selectFrom(sql`synopsis_characters USE INDEX idx_character_order`).as('sc')
        .select([
          ...
        ])
    )

    .with(
      'chars',
      db => db
        .selectFrom(sql`synopsis_characters USE INDEX idx_character_order`).as('sc')
        .select([
          ...
        ])
    )

    .with(
      'chars',
      db => db
        .selectFrom(sql`synopsis_characters USE INDEX idx_character_order`).as('sc')
        .select([
          ...
        ])
    )

but I get typescript error:

No overload matches this call.
  The last overload gave the following error.
    Argument of type 'RawBuilder<unknown>' is not assignable to parameter of type 'TableExpression<DB & { sections:
...

No overload matches this call.
  The last overload gave the following error.
    Argument of type 'RawBuilder<unknown>' is not assignable to parameter of type 'TableExpression<DB & { sections:
...

No overload matches this call.
  The last overload gave the following error.
    Argument of type 'RawBuilder<unknown>' is not assignable to parameter of type 'TableExpression<DB & { sections:
...

No overload matches this call.
  The last overload gave the following error.
    Argument of type 'RawBuilder<unknown>' is not assignable to parameter of type 'TableExpression<DB & { sections:
...

I can get it to work if I build the whole complete query with

sql`...`

sql`...`

sql`...`

sql`...`, but that brings about other issues such as how to use

.stream()

.stream()

.stream()

.stream() instead of

sql`...`.execute(db)

sql`...`.execute(db)

sql`...`.execute(db)

sql`...`.execute(db)

Any clues?

How do I specify a MySQL index hint?

Similar Threads