[Database] Flatfile or MySQL

What's the difference between storing data in an MySQL database, or in flatfiles? Other than the fact that administrators can easily see it, what is the differences?

Flatfiles tend to be slower. SQL queries are much faster than parsing strings in a file.

Both are save in a file, but MySQL has a pre-designed format for the file and is optimized at quickly finding things in that file. If you do a flat file, you have to write those optimizations yourself and think of a clever file format.

Not to mention flatfiles have concurrency problems: imagine two users want to open and write to the same file at roughly the same time. What happens is the second user finds the file locked, opens a new one, waits for the lock to be given away and overwrites the entire file with just one entry. Not an issue for a singleplayer game, but a definate buzzkill for anything you put online.

There are ways to prevent this of course, but all that is already done by your database engine as well, might as well make use of it.

Keep in mind that this does not hold if you only read from files: in that case, depending on your OS and database engine / connection, flatfiles can be even faster than a database. For this reason I often keep configuration data in config files.

FragFrog pretty much got the number. You'd generally use flatfile for small user applications, were there isn't too much to be written. As you read and write a lot more, it takes longer to get to a particular section of interest. I'd only use flatfile if it were a single-access application, and the file doesn't exceed 50 lines. Database applications like MySQL. Are nice, because you can loop the incoming data easily... for example, in java, you might have

PHP Code:

---

 private static ArrayList<String> getClientNames( Connection dbCon) {
    Statement query = dbCon.createStatement()j
    ResuktSet cNames = query.executeQuery("SELECT * FROM clients");
    Arraylist<String> clientNames = new ArrayList<String>();

    while (cNames.next()) {
    
        try {

            ClientNames.add(cNames.getString(1));
        } catch (java.sql.SQLException sqlE) {}

    return clientNames;
} 


---

Forgive me if something's wrong, I pulled that out of my ass... O'm not even gonna attempt to write the flatfile reader off of memory... but I can say that SQL makes large amounts of data relatively easy to handle.
Posted via Mobile Device

Quote:

---

Originally Posted by GhostSnyper

O'm not even gonna attempt to write the flatfile reader off of memory... but I can say that SQL makes large amounts of data relatively easy to handle.

---

For JAVA, config files can optionally be handled by a Properties object combined with a FileInputStream, something like this:

Code:

---

private void loadSettings (String name) {
        Hashtable<String, Properties> settings        =        new Hashtable<String, Properties> ();
  Properties  configuration = new Properties();
  try {
    configuration.load(new FileInputStream("conf/" + name + ".conf"));
  }
  catch (Exception e) {
    e.printStackTrace();
  }
  settings.put(name, configuration);   
}

---

Of course, that only allows for file reading :huh:

Really depends on what you're going to use it for, if it's something you would cache at start up, or something that usually will not change, binary is probably the best choice. Otherwise, a SQL based database would probably be best - binary files tends to slowdown and search-indexes struggle to find them when theres more and more binary files. For instance, lets say you are going to save and loading 1000 users, and (generally) your characters are saved in a file (1000 files), it would take longer for your computer to search though to get those 1000 files rather than searching in one file (a table) of a SQL database. I probably have misworded this :P