SQLite Forum

> Perhaps read up on Correlated sub-queries if what I say did not make immediate sense

So I did. A correlated sub query executes as if it runs once for each row in the outer query.

But "executes as if" and "actually executes" are not always the same. The database is allowed to perform whatever optimization it wants, as long as the observed behavior matches the expected one.

So does the working query below exhibits a quadratic behavior?

    CREATE TABLE chat (
      id INTEGER PRIMARY KEY
    );
    CREATE TABLE post (
      id INTEGER PRIMARY KEY,
      chatId INTEGER REFERENCES chat(id),
      date DATETIME
    );
    CREATE INDEX post_chatId ON post(chatId);

    EXPLAIN QUERY PLAN
    SELECT chat.*, post.* 
    FROM chat 
    JOIN post ON post.id = (SELECT id FROM post WHERE post.id = chat.id ORDER BY date DESC LIMIT 1);

I'm not quite used to query planning interpretation yet:

    QUERY PLAN
    |--SCAN TABLE chat
    |--SEARCH TABLE post USING INTEGER PRIMARY KEY (rowid=?)
    `--CORRELATED SCALAR SUBQUERY 1
       `--SEARCH TABLE post USING INTEGER PRIMARY KEY (rowid=?)