Best Practice for Processing dbase Data

[eluser]Fielder[/eluser]
Of these 2 ways, which in your opinion is better?

1. Use several sql statements to pull down data from the database, each statement running a slightly different query and letting SQL run the simple calculations and such? For instance

Code:

SELECT SUM(tot) AS n FROM table WHERE active = 1

Code:

SELECT * FROM table WHERE active = 1 GROUP BY 'title'

Code:

SELECT * FROM table WHERE active = 1 AND type = 1

...

2. Use 1 sql statement to pull down all the data, and create a helper or library with various functions with foreach loops to manipulate the returned results and get what I want?

Code:

function getTotal($array)
{
$total = 0;
foreach($array as $row)
{
if ($row['active'] == 1 $total++;
}
...
return $total;
}

Code:

function countType($array)
{
count($array);
return $count;
}

I suspect pulling everything down in 1 query and making my own functions is better because it is only one request to the mySQL server.

[eluser]brianw1975[/eluser]
This is where extending out your MySQL admin abilities and understanding how basic indexes and advanced indexes can be used to increase your database response times.

The first way will probably be faster overall if you set an index on 'active' in that table.

The same if you set and index on 'Title' for the second query

Ditto for an index on 'active' and 'type'

but you don't want to go making indexes on every column or every combination of columns, it increases the memory footprint of the DB and any indexes aren't used they are basically taking up valuable RAM.

Basically, if you have queries that get used on say more than 20% of your page views, analyze those queries and determine which index are needed and which can speed up the DB response and ultimately the page load time -- esp if those queries have "where this = that and the_other = another and yada = dada" you might want to consider making an index based on 'this', 'the_other', and 'yada'

I run a website that has a single table with about 175megs of data - just one table, it needs some severe normalization, but with the use of indexes I was able to increase the response time drastically on most pages that requested data from that table.

Here's some info on that table:

Code:

Space usage    
Type      Usage
Data     130,990     KiB
Index     45,779     KiB
Overhead     782,628     B
Effective     176,004     KiB
Total     176,769     KiB

As you can see, the Index file alone is 45 megs.

After reading this post (I suggest you read it) here I'll be reviewing my indexes to reduce that usage.

Single queries to the database with good indexes will be faster than a single query and a lot of processing.