SELECT Query still does a FULL TABLE SCAN. about libsql

[haaawk]

Please share the schema and queries you're using @3g015st

[3g015st]

@haaawk Sorry for the late reply, here it is:
Query:

  SELECT id, imageUrl, socialId, userType 
  FROM Users
  WHERE
  (
    userType = "SUBSCRIBED_PLAYER"
    socialId = <v4-uuid>
    AND DATETIME(scheduledBoostAt) >= CURRENT_TIMESTAMP

Schema

model Users {
  id              String            @id @default(uuid())
  socialId String @unique
  email           String            @unique
  scheduledBoostAt  DateTime?
  createdAt       DateTime          @default(now())
  updatedAt       DateTime?
  userType          String
  referralCode    String            @unique
  Items        Items[]
}

Query itself returns a single row on my app server, but when executed it's like 100 row reads on Turso analytics. Weird enough because I have 100-ish rows on that table, so maybe it is doing a FULL TABLE SCAN. But when I used the EXPLAIN QUERY PLAN it is using the index that I made.

[safojan]

is the issue still not assigned ??

[sivukhin]

@3g015st - can you share more details here? I don't quite understand your setup as you initially mentioned that you Made a compound index for socialId and userType but in your Prisma schema I don't see any compound indices - only unique indices over id, socialId, email & referralCode (also, your query has some imageUrl field but there is no such field in the schema...)

You can attach output of the following command with more details about your setup:

SELECT name, tbl_name, sql FROM sqlite_master WHERE tbl_name = 'Users'; SELECT sqlite_version();

Also, it will be great if you can attach query with EXPLAIN and EXPLAIN QUERY PLAN output (first one will print sqlite3 VM operations which can be helpful to track issue).

I also tried to reproduce your issue locally (with turso and against raw libsql with the test provided below) - but for me libsql accounted only 1 read row for the query you shared:

#[test]
fn test_stat() -> Result<(), Error> {
    let db = Connection::open_in_memory()?;
    db.execute(
        r#"
CREATE TABLE "Users" (
    "id" TEXT NOT NULL PRIMARY KEY,
    "socialId" TEXT NOT NULL,
    "email" TEXT NOT NULL,
    "scheduledBoostAt" DATETIME,
    "createdAt" DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updatedAt" DATETIME,
    "userType" TEXT NOT NULL,
    "referralCode" TEXT NOT NULL
);
"#, ()
    )?;
    for i in 0..100 {
        let id = i.to_string();
        db.execute(r#"INSERT INTO "Users" VALUES (?, ?, ?, datetime('2020-01-01T00:00:00'), datetime('2020-01-01T00:00:00'), datetime('2020-01-01T00:00:00'), ?, ?)"#, (&id, &id, &id, &id, &id))?;
    }
    db.execute(r#" CREATE UNIQUE INDEX "Users_socialId_userType_key" ON "Users"("socialId", "userType"); "#, ())?;
    let mut statement = db.prepare(
        "SELECT id, socialId, userType FROM Users WHERE (userType = '90' AND socialId = '90' AND DATETIME(scheduledBoostAt) >= CURRENT_TIMESTAMP)"
    )?;
    let mut result = statement.query(params![])?;
    while let Ok(Some(_)) = result.next() {}
    drop(result);

    assert_eq!(statement.get_status(rusqlite::StatementStatus::RowsRead), 1);
    Ok(())
}

[3g015st]

@sivukhin Sorry for the late reply but yeah you're right the SELECT query returns a single row, but when I visit the Turso analytics dashboard that same query returns the number of rows that the table has, instead of just "1". I haven't tried running the query locally. The contract's done on my end, so I guess we can close this out. Thank you all.