I am working an a personal project and I am stuck on choosing a database to use. What free database system would be suited for lots of small fast queries. The database will hold only minimal content and will be used to power the accounts portion of the site.
Also what backend language should I be looking at?. I have experience in PHP and MySQL as the database but I am trying to avoid those. I am looking at node.js for its speed but I have no idea what database to use. I am leaning on MongoDB.
As far as free databases go, I think you will find it very hard to exceed the performance of a well tuned MySQL database with query caching enabled and configured correctly.
But if your purpose is to just learn a different database, MongoDB is an excellent NoSQL DB. Just make sure you are very familiar with JSON before you start building your Data Model.
With regards to which language to use to interact with your database, obviously JavaScript is an easy fit for MongoDB, but here is where I will ask the question, what is your overall goal?
Is this just personal enrichment and you have no aspirations beyond this? Meaning you have no plans to makes software development a career.
If personal enrichment is your goal, then use whatever you like and JavaScript is a fine choice to use with MongoDB.
However, if your goal is to make software development a career, I would recommend using Java. As a software developer, learning Java is one of the best things you can do for your career, simply because Java is everywhere, and nearly every major and minor software company uses it in some capacity. I have written several previous posts about the ubiquitousness of Java in the software industry.
I currently work as an Android developer so I have a solid handle on Java. I am familiar with JavaScript and node seems like a good fit for the project.
I an trying to find a database that is good at handling multiple small requests. I am leaning toward MySQL as I am famillar with SQL.
The database won't have any content in it, just user information.
This does seem like a good opportunity to learn MongoDB. If you already know JavaScript well, determining how to store data in MongoDB will be pretty straightforward.
When you say lots of small requests, your real concern should be how many concurrent transactions you need to support, especially concurrent transactions against the same data, and concurrent transactions that will update the same data. That is where you will face many of your performance problems.
Depending on resources, both MongoDB and MySQL should be able to support at least 10 transactions a second--probably a lot more--without any demonstrable performance issues. Of course, this statement is a vast generalization. A lot will depend on how you configure your database, how you structure your data, user usage of your data, resources, how these interact together--especially during peak usage, and many other secondary and tertiary things. But "out-of-the-box" MySQL and MongoDB instances should be able to handle at least 10 "simple" transactions a second.
The only way to really know is to performance test your application. And both MongoDB and MySQL can be scaled to handle increased user loads.
The short answer is don't worry too much about which database is good at handling multiple small requests, both MongoDB and MySQL are good at it. Which is better is not an absolute and depends on many of the factors I listed above. If you actually have the choice, really the question should be which do you want to use.
screw server. based ones. sqlite is your solution.. entire database is a file. runs within your projects process. has a C/C++ interface as well as one for C#. java libraries can be found to interact with it as well http://sqlite.org
I always found familiarity bias to lead to bad decisions long term. "I am familiar with PHP and MySQL therefore its the best tool for the job."
Multiple small requests and SQL? SQL is inherently slow compared to document model. SQL works best with bulk batch requests. Not small requests. Sure with right caching etc its quick... but think of scalability, rapid growth - those are not fundamental design pillars of any SQL. What then? stacking Redis nodes on top of your MySQL?
And unless you absolutely need the extensive relational data mapping of SQL why use it at all. DOC DB's take less than a week to learn. For chat apps, games, etc - I'd recommend MongoDB or Couchbase. Couchbase scales linearly with plug and play ease. Runs javascript for index calculations and http for queries. Serves JSON. Its FAST. It has memcache. A track record of proven performance in viral growth deployments.
Up to you though, staying in comfort zone is always easy.
edit: Just a quick note, I am developing a messaging/social app right now with node/couchbase. Couchbase is a pleasure to develop against. Early node drivers sucked, but its solid now.