Re: index not being pick up

[Raf Mahn]

I tried reversing the join, unfortunately the index was still not picked up.

create or replace algorithm=merge view `vw_property`
as select property.*
FROM `flag` join property where (`property`.`scope_id` = flag.`scope_id` or flag.`override_enabled` = true)
with check option;

explain select * from vw_property where reference = "200ADELAIDE";

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE flag ALL scopeid_idx,override_enabled_idx NULL NULL NULL 1 NULL
1 SIMPLE property ALL uq_reference_idx NULL NULL NULL 5 Using where; Using join buffer (Block Nested Loop)

The presence of the union also prevents me from making the view updatable.

If I change the index to be (reference, scope_id), it will pick up index. Though, since scope_id is our column to make the table multi-tenancy, we wanted to keep it on the left as I'm hoping to be able to use the scope_id for other queries instead of needing to create more indexes in the future.

Seems like creating multiple views is way to go.