Posted in sqlite
6988
4:20 am, January 18, 2022
 

function to delete older sqlite records when the limit is reached

I had an issue where a sqlite database was hitting over 100,000 records and this was causing quite a lot of lag and crashing on my server, the number of records was not really meant to be this high, so i can just keep a few thousand so it does not cause lag.

This is the function i added and run before or after the latest item is inserted to remove older items in the database. 

PHP

// this will delete records over the specified number if run.
// it will keep the latest $records_to_keep number and delete older ones.
public function delete_over($records_to_keep = 1000) {
  $db_table_name = $this->db_table_name;
  $sql = "DELETE FROM $db_table_name WHERE ROWID IN (SELECT ROWID FROM $db_table_name ORDER BY ROWID DESC LIMIT -1 OFFSET $records_to_keep)";
  $result = $this->db->query($sql);
}

// example run on keywords class
$keywords->delete_over($records_to_keep = 1000);

External Link for function to delete older sqlite records when the limit is reached

View Statistics
This Week
94
This Month
730
This Year
645

No Items Found.

Add Comment
Type in a Nick Name here
 
Search Code
Search Code by entering your search text above.
Welcome

This is my test area for webdev. I keep a collection of code here, mostly for my reference. Also if i find a good link, i usually add it here and then forget about it. more...

Subscribe to weekly updates about things i have added to the site or thought interesting during the last week.

You could also follow me on twitter or not... does anyone even use twitter anymore?

If you found something useful or like my work, you can buy me a coffee here. Mmm Coffee. ☕

❤️👩‍💻🎮

🪦 2000 - 16 Oct 2022 - Boots
Random Quote


Bajo
Random CSS Property

inherit

The inherit CSS keyword causes the element for which it is specified to take the computed value of the property from its parent element. It can be applied to any CSS property, including the CSS shorthand all.
inherit css reference