Does anybody understand what the complexness is perfect for the SQL
LIKE operator which are more popular databases?
LIKE 'foo%' is very fast if operate on an indexed column. Climbing down the B-tree towards the correct position is an extremely fast operation. Then it is dependent how lengthy the number is.
LIKE '%foo%' has O(n) complexity. Whether it's your only criteria which means a complete table scan. For those who have other criterias that may by performed using indices, it'll only scan the the rows that remain following the initial blocking.
A hack that sometime can be used to quicken things would be to keep column backwards too:
col LIKE 'foo%' OR col_reverse LIKE 'oof%'
This method coupled with wise use of clustered indices may cause a significant speedup. This only works for brief fields, though, since MySQL includes a maximum index period of 1024 bytes (basically remember properly).
Is dependent around the RDBMS, the information (and perhaps size data), indexes and just how so on can be used (without or with prefix wildcard)!
You're asking too general an issue.
If you're asking concerning the performance impact:
The issue of like is it keeps the database by using a catalog. On Oracle It does not use indexes any longer (but I am still on Oracle 9). SqlServer uses indexes when the wildcard is just in the finish. I'm not sure about other databases.