Mysql user statistics

Question

Quick method description:

I have two tables, lets name them table ONE and table TWO. Every table record has an id of its author (user). There are no limits for records per user.

I would like to run a statistics page for each user.

For example, when John logs in:

John | 20 of A (list) | 150 of B (list)

Demo code

$a = mysql_query("SELECT * FROM A WHERE userID='$userID'");
$b = mysql_query("SELECT * FROM B WHERE userID='$userID'");

$a_num=mysql_num_rows($a);
$b_num=mysql_num_rows($b);

$echo "$userID | $a_num of A <a href='/a/$userID'>(list)</a> | $b_num of B <a href='/b/$userID'>(list)</a> ";

The question:

So, is check for number of records (every time user visits his stats page) an optimal solution when number of table records come to few hundred or maybe thousand? Is there any better way to run statistics? Maybe a separate table which counts records for each user?

Thanks :)

fvu · Accepted Answer · 2012-06-17 14:10:28Z

3

As you don't seem to be using the resultset itself,

$a = mysql_query("SELECT COUNT(*) FROM A WHERE userID='$userID'");

will get you the count you need in 1 step. Also, it's very likely a whole lot faster than what you're doing.

Second, the kind of optimisation you're thinking of is definitely possible (just a group by userID is all you need, and run that script every hour or day or whatever, depending on the required accuracy) but I'd really wait with that kind of stuff until you're dead sure you need it as it means extra work for the server and extra code to maintain.

Third and last, get rid of the deprecated mysql_query calls, and also look up prepared statements. A lot safer and not harder once you get used to them.

answered Jun 17, 2012 at 14:10

fvu

33.1k6 gold badges64 silver badges84 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

LeleDumbo · Accepted Answer · 2012-06-17 14:09:44Z

0

You could tell MySQL to count them for you:

SELECT COUNT(*) AS a_num FROM A WHERE userID='$userID'

This minimizes I/O between your application and MySQL server. Benchmark this yourself.

answered Jun 17, 2012 at 14:09

LeleDumbo

9,3804 gold badges26 silver badges38 bronze badges

Collectives™ on Stack Overflow

Mysql user statistics

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related