I'm creating a chat application, I have the page check for updates from my sqlite database every 10 seconds. I currently check for updates using a sqlite database, I just request posts that have a timestamp that is greater than my last check.
I know sqlite is pretty efficient because it uses indexes and caching. But I was wondering if I would be better off creating a txt file on my server that just stores a Unix Time Stamp in it. It would only be 10 bytes and then I would just update it with php every time a user posts. This way I could reload the txt file with ajax every 10 seconds and then make the sqlite query when there is a new post.
My question is which would less stressful on the server, a txt file or sqlite queries?
Note: I don't want to use long pulling, it's too complicated for me :}
Using a new file for each user or request will use far more resources than using the same SQLite file extended by each user.
Opening and closing files is expensive, and different files will not necessarily occupy the same place on disk and in the memory caches.
So, if your goal is to minimize the cost of these operations, use SQLite.
If you don't need persistence, you could as well keep that information in memory (SQLite supports in-memory tables).