CODEWITHSUNDEEP
sqlperformancesqlite
Marcus Frenkel
Marcus Frenkel

Reputation: 711

SQLite scanning table performance

My table has the following fields:

  1. Date (integer)
  2. State (integer)
  3. ProductId (integer)
  4. ProductName (integer)
  5. Description (text) (maximum text lenght 3000 characters)

There will be more than 8 million rows. I need to decide whether I should put the product description in another table. My main goal is to have this statement very fast: 

SELECT Date,State,ProductId,ProductName FROM tablename ORDER BY DATE desc LIMIT 100

The SQL result will not fetch the Description field value in the above statement. The user will see the description only when the row is selected in the application (new query).

I would really want to have the product Description in the same table, but I'm not sure how SQLite scans the rows. If Date value doesn't match I would assume that SQLite can quickly skip to the next row. Or maybe it needs to scan all fields of the row till it gets to the end of the Description field value in order to know that the row has ended? If it needs to scan all fields to get to the next row will the value of 3000 characters in the Description field decrease the speed a lot?

EDIT: No indexing should be used since INSERT speed is important.

EDIT: The only reason of trying to have it all in one table is that I want to do INSERTs and UPDATEs in one transaction of hundreds of items. The same item could be inserted and later updated in the same transaction, so I can not know the last insert id per item.

Upvotes: 1

Views: 626

Answers (2)

MPelletier
MPelletier

Reputation: 16657

I would suggest to rely on good old database normalization rules, in this case specifically 1NF. If that Description (same goes for the ProductName) is going to be repeated, you have a database design issue, and it being in SQLite or other has little to do with it. CL is right with his indexes, mind you, proper indexing will still matter.

Review your model, make a table for products and another for inventory.

Upvotes: 0

CL.
CL.

Reputation: 180020

  1. When you use that query and do not have an index on the Date column, SQLite will read all records from the table, and use a temporary table to sort the result.
  2. When you have an index on the Date column, SQLite will look up the last 100 records in the index, then read all the data of those records from the table.
  3. When you have a covering index, i.e., one index with the four columns Date, State, ProductId, and ProductName, SQLite will just read the last 100 entries from the index.

Whenever SQLite reads from the database file, it does not read values or records, but entire pages (typically, 1 KB or 4 KB).

In case 1, SQLite will read all pages of the table.
In case 2, SQLite will read the last page of the index (because the 100 dates will fit into one page), and 100 pages of the table (one for each record, assuming that no two of these records happen to be in the same page).
In case 3, SQLite will read the last few pages of the index.

Case 2 will be much faster than case 1; case 3 will be faster still, but probably not enough to be noticeable.

Upvotes: 1

Related Questions