SELECT Query still does a FULL TABLE SCAN.

[3g015st]

I'm not quite sure on why Turso still does a full table scan on my table. Here's the timeline of events.

1.) Created Users table, with columns userId, socialId and userType. Only index here is the userId column as the primary key. 2.) Table is now live at this point with user rows being created. 3.) Made a query to fetch users based on socialId and userType. 4.) Noticed on the Turso Analytics that the query is fetching 100 rows per execution, even though it's just returning a single row. 5.) Made a compound index for socialId and userType 6.) Observed the same query, now with the index in place but the results are still the same. 7.) Made a database branch for that database and this time I deleted all the rows for the Users table. Because I thought maybe the index won't work because of the existing rows. 8.) Observed the query again for the new database with deleted rows, same results.

I'm lost at this point and I'm thinking of moving forward with another provider. I am using Turso with prisma, but for the query itself I am using a raw sql query.

[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.