StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>You can use an implicit <a href="http://www.sqlite.org/lang_createtable.html#rowid" rel="noreferrer">"rowid"</a> column for that.</p> <p>Assuming you don't delete rows manually in different ways:</p> <pre><code>DELETE FROM yourtable WHERE rowid < (last_row_id - 1000) </code></pre> <p>You can obtain last rowid using <a href="http://www.sqlite.org/c3ref/last_insert_rowid.html" rel="noreferrer">API function</a> or as <code>max(rowid)</code></p> <p>If you don't need to have <em>exactly</em> 1000 records (e.g. just want to cleanup old records), it is not necessary to do it on each insert. Add some counter in your program and execute cleanup f.i. once every 100 inserts.</p> <h2>UPDATE:</h2> <p>Anyway, you pay performance either on each insert or on each select. So the choice depends on what you have more: INSERTs or SELECTs.</p> <p>In case you don't have that much inserts to care about performance, you can use following trigger to keep not more than 1000 records:</p> <pre><code>CREATE TRIGGER triggername AFTER INSERT ON tablename BEGIN DELETE FROM tablename WHERE timestamp < (SELECT MIN(timestamp) FROM tablename ORDER BY timestamp DESC LIMIT 1000); END </code></pre> <p>Creating unique index on timestamp column should be a good idea too (in case it isn't PK already). Also note, that SQLITE supports only <code>FOR EACH ROW</code> triggers, so when you bulk-insert many records it is worth to temporary disable the trigger.</p> <p>If there are too many INSERTs, there isn't much you can do on database side. You can achieve less frequent trigger calls by adding trigger condition like <code>AFTER INSERT WHEN NEW.rowid % 100 = 0</code>. And with selects just use LIMIT 1000 (or create appropriate view).</p> <p>I can't predict how much faster that would be. The best way would be just measure how much performance you will gain in your particular case.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload