mySQL a realistic means of frequent query/insertion?
I'm building an interactive piece that involves updating a database something like every 5 seconds for each user. I was thinking of using mySQL, but I don't know if it's built for such frequent access. Ideally, I'd like to refresh (insert/query) even more often... such as once a second... but would that cause any complications (other than synchronization issues, which are separate)?
If so -- what kind of alternatives do I have? I'd be building this app in Flash.
If you use MySQL, the tables that get updated a lot should probably be built as an INNODB type so that you can implement row-level locking, which would theoretically help prevent locking problems as multiple update requests come in. As to whether it ultimately can handle the load, there are too many variables for me to say (number of concurrent users/processes, lag time between clients and server, type of server-side programming and whether or not it uses persistent database connections, processing speed of the server(s) and whether it is dedicated to your application or if yours is just one of many apps running on it, etc.)
As far as alternatives: Can you install other DBMS's on your server, or are you limited to whatever is already installed? Is price an object (i.e.: is Oracle an option?)
"Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
~ Terry Pratchett in Nation
I'm be using my web host (http://www.lunarpages.com) for the server, so I don't have absolute control over it. Most of the work I've done up until now has not had to deal with persistent connections, INNODB, etc... so I'm pretty unfamiliar with it.
Could you provide some basic guidelines for this type of application, and possibly tutorial sites that could help me out? I realize that this may be more complicated than I imagined, so if it would be a huge endeavour, I might have to consider doing something else.
Budget is nonexistent, by the way, since this is for a relatively small school project.