24 ms·
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?
by calambrac 17y 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?
- zandorg 17y agoBasically, 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! :-)
- calambrac 17y agoThat'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 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.
- vomjom 17y agoI 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.