Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm trying to decide on the most efficient way to sort various data values. Here's how the data arrives: Device X sends a text string "name=value&name2=value&name=value"

On arrival that string is stuffed into a sql row along with the unique address of the sending device. This keeps data flowing in easily to a SQLite database.

My parsing script first gets all unique device addresses. Those are put in a hash for the parser and inserted into a new database. (the hash contains the rowid from the db after the insert.) (with more logic to keep race conditions out of the mix)

Then each row of string data is split up by the &, then by the =.

Here's the general table layout:

rawData(address, string, timestamp, processed)

Each row is read from the rawData, sorted and marked as processed. (makes it easy to muck around with the parsing script)

Data is placed into these:

devices(rowid, address)
dataNames(rowid, devicesid, name)
dataValues(nameId, value, timestamp)

I'm trying to decide on the most efficient method of inserting this data. It ends up as thousands of rows. I supposed I could keep a hash of known device/name pairs. Then if the hash doesn't know about a new name I can go ahead and insert it and refresh the hash...

Am I missing something totally obvious? The goal is to keep selects to a minimum for efficiency!

share|improve this question

migrated from stackoverflow.com Mar 11 '13 at 12:09

This question came from our site for professional and enthusiast programmers.

2  
have you looked into en.wikipedia.org/wiki/Database_normalization ? –  user16725 Mar 11 '13 at 4:39
    
The pass-through hash you describe could be useful. From a purely MySQL perspective, check out INSERT ... IGNORE. –  Alain Collins Mar 11 '13 at 18:48
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.