0

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.

Himanshu Mishra
  • 310
  • 3
  • 13
  • 1
    *Above query doesn't use any indexes* Of course, CONCAT() makes index usage impossible. *However running a query in this format, indexes are used.* `LIKE 'literal%'` expression may use index. *replacing `'ABC%'` with `concat('ABC', '%')` again causes indexes not being used.* Function again - expressions, even constant ones, are not evaluated during plan building process (except constant expressions which have boolean datatype and are used as an operand in logical expression). – Akina Jul 08 '20 at 06:21
  • *`name varchar(140) Primary Key`* VARCHAR as PK is not good solution... – Akina Jul 08 '20 at 06:23

0 Answers0