Cache XML files - Twitter API

[Mortekai]

I have built a small database which use the Twitter API to extract data from the listed members XML files (http://twitter.com/users/show.xml?screen_name=username) and it works well, but since the database makes several calls everytime a page loads I get kicked out very quickly, even with a small memberbase. So I wonder what solution would help with this problem?

 

I have considered dropping the XML data into the database and saving each XML file on the server, but I am not sure what solution would be best?

[lastkarrde]

I would parse the XML, then save the parsed data in a database. Make sure to include timestamps so you can regularly prune/empty/delete the database of old tweets.

[Mortekai]

So I would save the XML file as a string with a timestamp (in a different column I assume?) and then check each time the data is requested if the timestamp is x minutes different than current time and if so re-import the XML file again?

[lastkarrde]

Parsing XML is slow compared to a database SELECT.

 

Set up your DB tables so that it represents the XML schema. Pull the XML file in, parse it, and save the relevant information in it to the database. That way every time you want to reference that data, your not parsing an XML file (which takes time).

 

If your wanting to delete the old data, when you pull another/updated feed in, just do a DELETE where the timestamp is less than the current timestamp.