Flat Files vs. mySQL Database

Dixen

First a bit of a disclaimer... the following questions are going to sound rather newbish, but I have been using PHP for nearly 3 years now and am fairly versed in it's usage with a mySQL database, the same is not true with using flat files... I just never considered them in the first place.

Now... my query is an outright, which is better?

I recently became involved in a rather large project in which I am processing news and statistics for a text based strategy game and am now having SERIOUS execution time issues when trying to compile statistics on the fly from a couple of mySQL databases. One database has ~8000 records and the other can easily reach 250k records during each round of the game.

The suggestion was made by another developer that I am working with that we might try compiling the data used in gathering statisics in the background using flat files, then use the flat files (instead of mySQL tables) to grab the statistics on the fly (for Real Time stats).

As an example, on script that is currently taking more than 30 seconds to run is counting through 250k records up to 265 times in a single pass... now all this script is doing is counting news entries from the 250k rec table that "match" to the (up to) 265 members for a given organization in the game... in a "flat file" version of this script it would open 265 files (using file()) and the count the number of records in the file... In this case I think it's obvious that the flat file version would run faster, but then most of the processing would happen in the background before the script would ever be run by someone viewing the page.

As a last tid bit of information if I were to go with the flat file set up we have figured (rough guesstimate) that we'd have some 30-40k (yes, 40k actual files... not files sizes) that we'd be using... going on the assumption that storage would not be an issue is this something to consider?

Thoughts, comments, sarcastic remarks?

Thanks for your inputs. 🙂

Weedpacket

Short answer: database. You don't really think that banks and airlines and so on store their data in flat files, do you? PostgreSQL's website claims that 4TB PostgreSQL databases exist.

For flat files you're going to end up writing your own database management system. And the overhead of opening and reading (and searching) hundreds of files is going to be enormously slower than the b-tree indexed files of a real DBMS.

Of course, that's all assuming you build a decent database; a sensible and normalised schema, proper indexes, views, stored procedures, triggers, and so forth. And that's an entire subject on its own.

puntloos

I've been teaching myself PHP (probably badly) at break-neck speed over the last 2 months. Before that I did most of work with shell scripts.

If I look at your example the first thing I thought was:

grep -c "NEWS:" bigfile

Would take, depending on your harddisk, the time to run through an entire file. I have an access log file for one of my websites that totally accidentally had 250000 lines of output. (The file is 52Mb)

That run took under 2 seconds.

If then your 'NEWS:' entries make up the minority of the file, the 'filtered' file would take even less time to run through.

In the end though, a database scales better if you want to do more and more stuff with the same file.

drawmack

I've seen mysql databases larger then the one you're describing run just fine. My guess is that you're not using the power of sql properly, you're database isn't properly normalized, indexed and repaired.

For example you're pulling 265 queries when you could just construct an array of the people from a single query then use the IN operator to pull a single query on the news table.

Dixen

I make no bones about being a total newb to SQL... I have only ever done simple selects, inserts, deletes and updates to records... most of what I have done thusfar using the SQL DBs was to display data that I had already processed into a database.

This is the function I'm using in order to grab the top 10 attackers from the database... This function times out every time it runs (30secs). I use the same structure in other places in my code and the only difference is the sheer volume of SQL queries made. The only index I have on the scores and news tables are on the "id" field.

function attackers($criteria,$temptag) {
    global $module_name, $db, $prefix;
    opentable();
    print "
		<center><big><b>Top 10 Attackers:</b></big></center><br><br>
		<table border='1'><tr>
		<td width='25' align='center'>Rank</td>
		<td width='250' align='center'>Country</td>
		<td width='50' align='center'>Tag</td>
		<td width='50' align='center'>SS-PS</td>
		<td width='50' align='center'>GS-BR-AB</td>
		<td width='50' align='center'>NM-CM-EM</td>
		<td width='50' align='center'>Total</td></tr>";
    $alphanum = $db->sql_numrows($alpharesult = $db->sql_query("SELECT * FROM x_scores WHERE tag='$temptag'"));
    $a = 0;
    while ($a < $alphanum) {
     $alphalist[$a]['country'] = mysql_result($alpharesult,$a,"country");
     $alphalist[$a]['countrynum'] = mysql_result($alpharesult,$a,"countrynum");
     $alphalist[$a]['tag'] = mysql_result($alpharesult,$a,"tag");
     $a++;
    }
    $b = 0;
    while ($b < $alphanum) {
     $country = $alphalist[$b]['country'];
     $countrynum = $alphalist[$b]['countrynum'];
     $tag = $alphalist[$b]['tag'];
     $alphalist[$b]['lgcount'] = 0;
     $alphalist[$b]['speccount'] = 0;
     $alphalist[$b]['misslecount'] = 0;
     $betanum = mysql_numrows($betaresult = $db->sql_query("SELECT code FROM x_earthnews WHERE attcountry='$country' && atttag='$tag'"));
     $x = 0;
     while ($x < $betanum && $betanum > 0) {
      $code = mysql_result($betaresult,$x,"code");
      switch($code) {
      case 1:
      case 2:
       $alphalist[$b]['lgcount']++;
      break;
      case 5:
      case 6:
      case 7:
       $alphalist[$b]['speccount']++;
      break;
      case 10:
      case 11:
      case 12:
       $alphalist[$b]['misslecount']++;
      break;
      }
     }
     $alphalist[$b]['count'] = $alphalist[$b]['lgcount']+$alphalist[$b]['speccount']+$alphalist[$b]['misslecount'];
     $b++;
    }
    array_csort($alphalist, 6, 'desc');
    $i = 0;
    while ($i < 10) {
 	   $rank = $i + 1;
     $country = $alphalist[$i]['country'];
     $countrynum = $alphalist[$i]['countrynum'];
     $tag = $alphalist[$i]['tag'];
     $lgcount = $alphalist[$i]['lgcount'];
     $speccount = $alphalist[$i]['speccount'];
     $misslecount = $alphalist[$i]['misslecount'];
     $count = $alphalist[$i]['count'];
     print "
     <tr><td align='center'>$rank</td>
		 <td align='middle'><a href='modules.php?name=Scores&amp;func=viewcountry&amp;countrynum=$countrynum'>$country</a>
		 <td align='center'>&nbsp;<a href='/modules.php?name=Scores&amp;func=search&amp;criteria=$tag'>$tag</a></td>
		 <td align='center'>$lgcount</td>
		 <td align='center'>$speccount</td>
		 <td align='center'>$misslecount</td>
		 <td align='center'>$count</td></tr>";
		 $i++;
		}
    echo "</table>";
	  closetable();
}

To give you a quick run down of the function I am first building an array of countries that are members of a given organization. That array is built from an 8000 record DB... at this time the largest org is about 265 members. Then, using they array built I count on 4 different attack types from the "code" field in the table that is roughly 200k records as of now, but could go to 300k+ records without too much of a problem...

So, I end up with up to 265 queries into 200k+ records and the script never finishes. So, can anyone offer any help to streamline this code?