What is the syntax to force the use of an index for a join in MySQL

Question

The use of the "FORCE/USE/IGNORE INDEX" when doing a straightforward select is well-documented, but it's not clear from the documentation how to do it for a JOIN.

How do you force a specific index to be used for a joined table?

Answer 1

The FORCE/USE/IGNORE goes after the table name you are joining, and after the alias if you're using one.

SELECT
  t1.`id` AS `id_1`,
  t2.`id` AS `id_2`
FROM
  `table1` t1
LEFT OUTER JOIN
  `table2` t2
  FORCE INDEX FOR JOIN (`table1_id`)
  ON (t2.`table1_id` = t1.`id`)