How to give an index hint to MySQL through Ecto

Question

I need to hint an index to MySQL in a left join through Ecto and it's being pretty difficult.

The closest I've got is this:

query = from(s in Dossier.School,
    left_join: sc in fragment("subscription_coordinators use index (subscription_coordinators_school_products_idx)"),
    on: fragment("school_id = ?", 1)
        and fragment("product in ('reading', 'maths')")
        and is_nil(sc.deleted_at),
    select: s,
    where: s.id == 1
  )
# Ecto.Adapters.SQL.to_sql(:all, Dossier.Repo, query)
query |> Dossier.Repo.all

in IEx that compiles and generates SQL fine:

SELECT s0.`id`, s0.`name`, s0.`school_code`, s0.`deleted_at`, s0.`district_id` 
FROM `schools` AS s0 
  LEFT OUTER JOIN (subscription_coordinators use index (subscription_coordinators_school_products_idx)) AS f1 ON (school_id = 1 AND product in ('reading', 'maths')) AND f1.`deleted_at` IS NULL WHERE (s0.`id` = 1)

but obviously the position of the table alias is wrong so actually running the query produces:

** (Mariaex.Error) (1064): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'f1 ON (school_id = 1 AND product in ('reading', 'maths')) AND f1.`deleted_at` IS' at line 1
    (ecto) lib/ecto/adapters/sql.ex:436: Ecto.Adapters.SQL.execute_and_cache/7
    (ecto) lib/ecto/repo/queryable.ex:130: Ecto.Repo.Queryable.execute/5
    (ecto) lib/ecto/repo/queryable.ex:35: Ecto.Repo.Queryable.all/4

The full query is much bigger with nearly a dozen left joins (though this is the only one that needs a hint) and all in Ecto. I want to keep the fragment as small as possible if it can be done.

This is only a temporary solution though. We'll ANALYZE TABLES soon so it's unnecessary.

Answer 1

It is not supported currently. Please open up an issue in the Ecto repository and we will discuss it. Thank you!