Comment by calambrac
17 years ago
Can you expand a bit on the inner join issue, how you fixed it, and when and how the author refused to accept that it was a bug?
17 years ago
Can you expand a bit on the inner join issue, how you fixed it, and when and how the author refused to accept that it was a bug?
Basically, I can't go into it properly because the project is confidential, and I can't think of a similiar table example.
In SQLite:
2 inner joins took about 10 seconds. 3 inner joins in one query were taking 10 minutes.
I was using inner joins to filter - ie shrink the table - when a row had multiple fields in one column (eg, a house has 1 front door but 3 bathrooms).
What should happen in SQLite is that each progressive inner join should make the search table smaller, not bigger. So a 3-join query should be quicker than a 2-join query.
The solution was to create some temp tables, do 1 inner join at a time, and shrink the temp tables down myself.
Now a query with 4 inner joins takes 8 seconds, without changing a line of SQLite. You can't tell me that's not a bug! :-)
That's not a 'bug', it's the expected behavior of the current design and implementation. From the optimization overview (http://www.sqlite.org/optoverview.html):
The current implementation of SQLite uses only loop joins. That is to say, joins are implemented as nested loops.
Know your tools.
I think you misunderstand the goals of sqlite, which is why you think it's a bug and the author does not.
Sqlite doesn't intend to include a very smart query planner simply because that would make it much larger and more complex. That means it'll do stupid things for some queries sometimes, but in return, you get a small library that's quick for common queries.