RE search is a lot slower than exact match?

Neo4j Graph Platform
1 
profile
MATCH (m:Brand)
WHERE  m._name= 'Apple'
RETURN properties(m) as properties LIMIT 3

This takes 4 ms and only 2 db hits.

But this RE search takes 176 ms with 94361 db hits in my graph.

 profile
MATCH (m:Brand)
WHERE  m._name=~ '(?i)'Apple'
RETURN properties(m) as properties LIMIT 3

So RE searches doesn't use indexing. Is this comparison normal?

2

It depends on how the regexp is implemented. So, I'm speculating here....

I suppose that a clever regexp implementation could recognize that you are searching for a 5 or 6 letter string, and discard strings that are shorter than 5 letters and then check the matches.

You might be able to optimize this (assuming short circuit logic)

WHERE m._name CONTAINS 'Apple' and m._name m._name=~ '(?i)'Apple'

or maybe if this does what you want (and it depends in their index indexes the reverse of the string to be able to find from the end of the string efficiently.)

WHERE m._name ENDS WITH 'Apple' and m._name m._name=~ '(?i)'Apple'

Otherwise, the regexp expression is probably trying to do a match on every m._name which is a linear search.

Not an easy problem to solve....