MySQL ignore index even with use index or force index

Question

Here I'll add part of my query because it's sensitive information and I will use not real names:

SELECT
  `entity`.`id`,
  `entity`.`requirements`,
  `entity`.`description`,
  `entity`.`status`,
  `entity_videos`.`length`,
  `entity_videos`.`quality`,
  `states`.`name`,
  `uploads`.`id`,
  `uploads`.`name`
    FROM  `entity`
    LEFT JOIN  `states`  ON `states`.`id` = `entity`.`state_id`
    INNER JOIN  `uploads` FORCE INDEX (`uploadable_id_index`)
       ON  `uploads`.`uploadable_type` = 'Entity'
      AND  `uploads`.`category` = 'Icon'
      AND  (`uploads`.`uploadable_id` = `entity`.`id`
              OR  `uploads`.`uploadable_id` = `entity`.`parent_entity_for_icon`
           )
    INNER JOIN  `entity_videos` FORCE INDEX (entity_videos_entity_id_index)
       ON `entity_videos`.`entity_id` = `entity`.`id`
    WHERE  `entity`.`status` = 'active' 

The issue is that Mysql optimizer do not want to use uploadable_id_index index. Part of explain:

As I know FORCE INDEXis force optimizer to use index except for situation that optimizer can't use the index. What should I do to force that index and do not make a full scan of the table? I tried to remove entity_videos_entity_id_index also I tried to add uploads table information to where clause, but nothing works for me. Any ideas? Thank you very much for any help

Updated:

With help of @Barmar and @PaulSpiegel I found that issue is in (uploads.uploadable_id = entity.id OR uploads.uploadable_id = entity.parent_entity_for_icon). And playing with query for some time I found that best solution in my case is:

 SELECT
  `entity`.`id`,
  `entity`.`requirements`,
  `entity`.`description`,
  `entity`.`status`,
  `entity_videos`.`length`,
  `entity_videos`.`quality`,
  `states`.`name`,
  `icon`.`id`,
  `icon`.`name`,
  `parent_offer_icon`.`id`,
  `parent_offer_icon`.`name`
    FROM  `entity`
    LEFT JOIN  `states`  ON `states`.`id` = `entity`.`state_id`
    LEFT JOIN  `uploads` as `icon` FORCE INDEX (`uploadable_id_index`)
       ON  `icon`.`uploadable_type` = 'Entity'
      AND  `icon`.`category` = 'Icon'
      AND  `icon`.`uploadable_id` = `entity`.`id`
    LEFT JOIN  `uploads` as `parent_offer_icon` FORCE INDEX (`uploadable_id_index`)
       ON  `parent_offer_icon`.`uploadable_type` = 'Entity'
      AND  `parent_offer_icon`.`category` = 'Icon'
      AND  `parent_offer_icon`.`uploadable_id` = `entity`.`parent_entity_for_icon`
    INNER JOIN  `entity_videos` FORCE INDEX (entity_videos_entity_id_index)
       ON  `entity_videos`.`entity_id` = `entity`.`id`
    WHERE  `entity`.`status` = 'active'
      AND  (parent_offer_icon.id IS NOT NULL
              OR  icon.id IS NOT NULL ) 

I'm still open for other suggestions :)

Answer 1

Let's start by turning the pesky OR into UNION:

( SELECT  e.id AS eid,
          u.id AS uid,
          u.name AS uname
    FROM `uploads` AS u 
    INNER JOIN  `entity` AS e
       ON  u.`uploadable_id` = e.`id`
    WHERE  u.`uploadable_type` = 'Entity'
      AND  u.`category` = 'Icon'
      AND  e.status = 'active'
) UNION DISTINCT
( SELECT  e.id AS eid,
          u.id AS uid,
          u.name AS uname
    FROM `uploads` AS u 
    INNER JOIN   `entity` AS e
       ON  u.`uploadable_id` = e.`parent_entity_for_icon` 
    WHERE  u.`uploadable_type` = 'Entity'
      AND  u.`category` = 'Icon'
      AND  e.status = 'active'
)

That will need some of these indexes:

uploads:  INDEX(uploadable_type, category, uploadable_id, id, name)
entity:   INDEX(parent_entity_for_icon, status, id)

(I assume entity has PRIMARY KEY(id)? Please provide SHOW CREATE TABLE so I don't have to guess.) If there are problems with such a long index, let me know; I can probably provide a workaround.

By turning the OR into a UNION, different indexes can be used for each part. With OR, the Optimizer usually punts and does something inefficient.

Please verify that the query above runs fast and produces reasonable output. Then...

SELECT  e.`id`, e.`requirements`, e.`description`, e.`status`,
        ev.`length`, ev.`quality`,
        s.`name`,
        uid, uname
    FROM ( the-query-above ) AS i
    JOIN `entity` AS e  ON e.id = i.eid
    LEFT JOIN  `states` AS s  ON s.`id` = e.`state_id`
    INNER JOIN  `entity_videos` AS ev  ON ev.`entity_id` = i.eid

Other than PRIMARY KEY(id) on each table, you will need

entity_videos:  INDEX(entity_id)  -- good, or
entity_videos:  INDEX(entity_id, length, quality)  -- better ("covering")

Don't use FORCE INDEX.

More on index creation: http://mysql.rjweb.org/doc.php/index_cookbook_mysql