I only have ever used mysql and storing data on .txt files.

Currently I'm working with tick data (I have node.js on my server). My custom setup so far stores a fixed amount of tick data as comma separated values on a txt file (I use javascript to add new vaules and delete old). The setup can't safely collect as much info as I would like and occasionally a 'server hic-up' happens and the data gets lost/damaged. It takes a long time to 're-grow' all the data, so I need to get something more robust (this way is too fragile).

How can I achieve a data structure that fits this description?

The database should store prices that are associated with unix timestamps oldest to newest. the database should have a maximum number of rows (100,000). If adding one new row would put the database over its maximum size then it should delete its oldest row.

My thoughts are: Could I set the primary key to be the timestamp? How would I handle keeping the database to a set amount of rows? what would the speed be if I get all the rows at once in a query? What database language would be suited for this?

I only have 3-4 users that will access this app so there wont be a lot of querys. txt files are defiantly not good for (me) storing large data.

Any advice?

  • Yes, depends on your database of choice but a trigger or stored procedure could do it, it depends (but fast overall), SQL most likely unless you want to try using NoSQL (like MongoDB) to leverage your JavaScript familiarity. Your use case has such small data that basically anything works, go ahead and use MySQL if you're most familiar with it. – LowlyDBA Apr 1 '15 at 18:49
  • mysqltutorial.org/… like this? – Ben Muircroft Apr 1 '15 at 18:56
  • 1
    Yes. Within a stored procedure you can include the logic to insert a new row, then check the total num of rows in the table and if > 100,000, then delete the oldest row. – LowlyDBA Apr 1 '15 at 19:02
  • @John I changed my mind, I'm using MongoDB instead, really cool! – Ben Muircroft Apr 2 '15 at 9:18

Could I set the primary key to be the timestamp?

Yes, the primary key can be any column but I think you'd be better off using an ID column for the primary key to ensure it's unique (which is technically impossible to gaurantee with a timestamp primary key). As ypercube suggests, a PK of (timestamp, id) and using milliseconds on the time value may be a good option as well when using InnoDB and version 5.6+.

You can still create an index on the datetime column which will give similar performance, though if you're pulling back all rows all the time it's hardly an issue to begin with.

How would I handle keeping the database to a set amount of rows?

You can use a stored procedure to handle multiple pieces of logic, such as

  • Insert new row
  • Check table row count
    • If > 100,000 delete oldest row

Or, you can use a trigger which would have similar logic and automatically fires for every insert on the table.

what would the speed be if I get all the rows at once in a query?

100,000 rows isn't that many in terms of a relational database, so you shouldn't have retrieval time being an issue.

What database language would be suited for this?

Again, it's a small size so pretty much anything works. MySQL seems like an obvious choice if you have previous experience with it.

|improve this answer|||||
  • very helpful advice! though you mentioned using an id for primary unique key. if it auto increments and i add a row every 30 seconds the id number will quickly become a high number. does that even matter? i have only ever worked with small projects. should this be a concern or is it irrelevant? – Ben Muircroft Apr 1 '15 at 19:28
  • 2
    The max size of the INT data type is 2147483647 and BIGINT is 9223372036854775807, so check your math and see if you'll run out of numbers for those or not :) – LowlyDBA Apr 1 '15 at 19:32
  • lol irrelevant. – Ben Muircroft Apr 1 '15 at 19:35
  • 1
    Using a TIMESTAMP as the PRIMARY KEY runs a risk: What if two INSERTs happen in the same second? You will get a "duplicate key" error and the second one won't be inserted. – Rick James Apr 1 '15 at 20:13
  • 1
    Another good option for the primary key if the data is inserted in chronological order and you use InnoDB ,(which you should), is the combination of (timestamp, table_id), where table_id is an auto_incremented column. If you are in 5.6 version, you can also use timestamps with a millisecond (or even microsecond) accuracy. The probability that you run into duplicate key error still exists though. – ypercubeᵀᴹ Apr 1 '15 at 20:14

Following John's advice;

"try using NoSQL (like MongoDB) to leverage your JavaScript familiarity"

I have been looking at MongoDB tutorials http://www.tutorialspoint.com/mongodb/mongodb_create_collection.htm and they have an example of a capped collection (table) that auto deletes old documents (rows) if the collection is 1000 documents long. This is a built in feature. So I will be dropping MySql now for MongoDB

>db.createCollection("mycol", { capped : true, autoIndexID : true, size : 6142800, max : 10000 } )
{ "ok" : 1 }
|improve this answer|||||
  • Cool, I didn't know that was a feature - glad it worked out for you. – LowlyDBA Apr 2 '15 at 12:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.