Kysely•9mo ago

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 ...):

  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([
          ...
        ])
    )

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:
...

I can get it to work if I build the whole complete query with sql`...`, but that brings about other issues such as how to use .stream() instead of sql`...`.execute(db) Any clues?

3 Replies

cheekybuddha•9mo ago

OK, I guess this is not possible in kysely. I solved it by creating a view on the server, which makes the kysely code a lot simpler! Many thanks for a great bit of software!

koskimas•9mo ago

It's possible. You just need to provide the row type explicitly. Kysely can't parse types from raw SQL. https://kyse.link/qD6b7

cheekybuddha•9mo ago

Thank you, and apologies for my impatience! Unfortunately the generated SQL in not correct (for MySQL), or at least I can't get it to work. With the example in your link:

const rows = await db
  .selectFrom(sql<Person>`person use index foobar`.as('person'))
  .innerJoin("pet", "owner_id", "person.id")
  .where("first_name", "=", sql.lit("Jennifer"))
  .where("species", "=", 'cat')
  .select(["first_name", "pet.name as pet_name"])
  .execute();

const rows = await db
  .selectFrom(sql<Person>`person use index foobar`.as('person'))
  .innerJoin("pet", "owner_id", "person.id")
  .where("first_name", "=", sql.lit("Jennifer"))
  .where("species", "=", 'cat')
  .select(["first_name", "pet.name as pet_name"])
  .execute();

produces:

SELECT
  `first_name`,
  `pet`.`name` AS `pet_name`
FROM
  person
USE INDEX foobar AS `person`
INNER JOIN `pet` ON `owner_id` = `person`.`id`
WHERE
  `first_name` = 'Jennifer'
  AND `species` = ?

SELECT
  `first_name`,
  `pet`.`name` AS `pet_name`
FROM
  person
USE INDEX foobar AS `person`
INNER JOIN `pet` ON `owner_id` = `person`.`id`
WHERE
  `first_name` = 'Jennifer'
  AND `species` = ?

however I think it needs to be:

-- ...
FROM
  person AS `person`
USE INDEX (foobar)
-- ...

-- ...
FROM
  person AS `person`
USE INDEX (foobar)
-- ...

I can add the parentheses in the sql`` call, but adding the alias in there too causes typescript to have a fit (No overload matches this call.) Am I missing something? TIA

Gaming

Programming

How do I specify a MySQL index hint?