3 ms·

> SQLite does Nested Loop join Only that? Never anything better? Really? EDIT: Really. Section titled Joins here https://sqlite.org/optoverview.html https://

> SQLite does Nested Loop join Only that? Never anything better? Really? EDIT: Really. Section titled Joins here https://sqlite.org/optoverview.html https://sqlite.org/optoverview.html states: "SQLite implements joins as nested loops." That's quite shocking. While doing MySQL and Postgres when nested loop showed up in EXPLAIN in almost all cases I knew I botched my query and/or indexes.

bawolff 2y ago
If you mean in mysql explain: "Using join buffer (Block Nested Loop)", its not slow because nested loop algorithm is being used, its slow because of the join buffer part, which is an optimization used when its not possible to immediately get the right row of the inner table via an index. As far as i know (might be wrong,im not really familiar with mysql internals), mysql (like sqlite) generally uses nested loop joins all the time. The EXPLAIN just only says something in the join buffer case. When using a simple nested loop join, EXPLAIN does not mention the fact that it is using that algorithm.
- scotty79 2y ago
  Fair enough, most of my memories about building fast, complex queries come from my Postgres times. Though I remember one instance where while using MySQL in a web app it turned out that N+1 was faster than doing a JOIN.
  - sgarland 2y ago
    Then your table schema was likely sub-optimal.
    - scotty79 2y ago
      It was long time ago. The schema was basically one table with a lot of fields and records and multiple small tables with way fewer records. Query in question resulted in small number of rows (because of LIMIT clause). It was faster to get rows from big table and then run few additional queries to get data for found rows from smaller ones than join everything together in one query. Maybe the nested loop buffer was culprit or whatever. Maybe I ran into edge case of query planner MySQL had 20 years ago. Who knows.

bawolff 2y ago

If you mean in mysql explain: "Using join buffer (Block Nested Loop)", its not slow because nested loop algorithm is being used, its slow because of the join buffer part, which is an optimization used when its not possible to immediately get the right row of the inner table via an index. As far as i know (might be wrong,im not really familiar with mysql internals), mysql (like sqlite) generally uses nested loop joins all the time. The EXPLAIN just only says something in the join buffer case. When using a simple nested loop join, EXPLAIN does not mention the fact that it is using that algorithm.

scotty79 2y ago
Fair enough, most of my memories about building fast, complex queries come from my Postgres times. Though I remember one instance where while using MySQL in a web app it turned out that N+1 was faster than doing a JOIN.
- sgarland 2y ago
  Then your table schema was likely sub-optimal.
  - scotty79 2y ago
    It was long time ago. The schema was basically one table with a lot of fields and records and multiple small tables with way fewer records. Query in question resulted in small number of rows (because of LIMIT clause). It was faster to get rows from big table and then run few additional queries to get data for found rows from smaller ones than join everything together in one query. Maybe the nested loop buffer was culprit or whatever. Maybe I ran into edge case of query planner MySQL had 20 years ago. Who knows.