Hacker News Reader

Generally any equality is fine, but you'd usually do something like on mycustomfunction(a) = mycustomfuntion(b)

And if you do this, it will be incredibly slow because you cant use any indexes in this case.

Good news! Many database systems support indexes on functions, including on user-defined functions. The only one I've used is Postgres[0] but apparently MySQL added support recently as well[1]

[0] https://www.postgresql.org/docs/current/indexes-expressional...

[1] https://dev.mysql.com/doc/refman/8.0/en/create-index.html#cr...

SQLite has it too (caveats, like always, apply): https://www.sqlite.org/expridx.html.

That's cool, but then I'd assume you have to make an index for each function. In general if a person is asking basic questions about sargability, I will steer them away from functions on a predicate (it's usually my last choice.)

Often you can just think a bit harder about your set based logic and make an index that's reusable by other queries.

In other SQL's you can also make a materialized view, which may be able to persist some of these things, its just like keeping n+1 copies of your data to satisfy a different set of relations/projections.

You can create indexes on expressions, like mycustomfunction(a), in Postgres at least.

Reply on news.ycombinator.com