I have a table with following schema
tabFoo
name varchar(140) Primary Key
This table has following data, repeating multiple times
ABC
ABC - XYZ
ABC - EFG
ABC - IJK
ABC - GHI
ABC - MNO
similarly this repeats many times as following with DEF taking many different values (10000+ values)
DEF
DEF - XYZ
DEF - EFG
DEF - IJK
DEF - GHI
DEF - MNO
However, running a query like
select
main.name, group_concat(distinct secondary.name) as secondary_names
from
tabFoo main
left join
tabFoo secondary
on
secondary.name like concat(main.name, '%')
where
main.name not like '%- XYZ'
and main.name not like '%- EFG'
and main.name not like '%- IJK'
and main.name not like '%- GHI'
and main.name not like '%- MNO'
Above query doesn't use any indexes, and runs an ALL scan for secondary
table.
However running a query in this format, indexes are used.
select
main.name, group_concat(distinct secondary.name) as secondary_names
from
tabFoo main
left join
tabFoo secondary
on
secondary.name like 'ABC%'
where
main.name not like '%- XYZ'
and main.name not like '%- EFG'
and main.name not like '%- IJK'
and main.name not like '%- GHI'
and main.name not like '%- MNO'
However replacing 'ABC%'
with concat('ABC', '%')
again causes indexes not being used.
Why does mariadb drops index usage when using concat? Function is not being applied to searched column.