logoalt Hacker News

lovasoa • 11/20/2024 • 3 replies • view on HN

The topic of huge queries on tiny databases makes me think of this recent discussion on the SQLite forum: https://sqlite.org/forum/forumpost/0d18320369

Someone had an issue because SQLite failed to optimize the following query

    select * from t where x = 'x' or '' = 'x'
Someone said that SQLite could not optimize out the "or '' = 'x'" because it would be too expensive to compute. Which is obviously true only for huge queries on tiny datasets.

Replies

jiggawatts • 11/20/2024

> SQLite

Well... there's your problem. SQLite is not a general-purpose RDBMS, it is marketed as a replacement for "fopen()", a purpose for which it excels.

A similar product is the Microsoft Jet database engine, used in products such as Microsoft Exchange and Active Directory. Queries have to be more-or-less manually optimised by the developer, but they run faster and more consistently than they would with a general-purpose query engine designed for ad-hoc queries.

➕ show 1 reply
recursive • 11/20/2024

It's not obviously true at all. Optimizing out `'' = 'x'` can be done for a fixed cost regardless of record count.

➕ show 1 reply
hinkley • 11/20/2024

Why would it be too expensive to optimize out static subexpressions?

➕ show 1 reply