many mysql queries or one to two foreach statments

[c_shelswell]

Hi i'm just wondering - i've got a statistics page to make where i need to get some numerical data from my database and some i need to count the number of occurences of a word. I need to get bits from a few different tables with about 80k+ records in some of them.

 

I could get most of the data in 1 query i think but then i'd have to run a foreach to count the data or I could do about 10 seperate queries.

 

What do you think would be quicker?

 

Cheers

[The Little Guy]

or...

 

<?php echo mysql_num_rows($sql); ?>

[Barand]

Whenever I'm faced with that question I do this

 

$t1 = microtime(true);

// do it one way a few hundred times

$t2 = microtime(true);

// do it other way same number of times

$t3 = microtime(true);

echo 'Method 1 ', $t2-$t1, '<br/>';
echo 'Method 2 ', $t3-$t2, '<br/>';

[c_shelswell]

good idea i'll give that a go. Didn't know if it was a case of "sql is always quicker" or vice versa

 

thanks for your help

[cooldude832]

it tends to be faster if you work all the data out of the database in a manner that suites your needs best

meaing do COUNT, MAX, MIN, AVG, UINQUIE, etc. in mysql and not in php as this waste resources.

 

Also 1 strong query can usualyl replace a ton of weak ones.

[c_shelswell]

thing is i need to to a sum(all_subscribed) from one table then another count(users_with_only_1email_error) so my query would end up $sql = "select sum(subscribed), count(mail_delivery_errors) from table1, table2 where mail_delivery_errors='perm'" so it's kind of not going to work i think.

 

I could do one or two big queries select * then foreach all my data. I think the queries will probably be the easier bet though.

 

Cheers

[Barand]

If you join the tables and have multi matching records you double up the totals