Most effiecient way to display some stats using PHP/MySQL

Question

I need to show some basic stats on the front page of our site like the number of blogs, members, and some counts - all of which are basic queries.

Id prefer to find a method to run these queries say every 30 mins and store the output but im not sure of the best approach and I don't really want to use a cron. Basically, I don't want to make thousands of queries per day just to display these results.

Any ideas on the best method for this type of function?

Thanks in advance

UPDATE: Best way i found was to store the stats in cache for 30 mins. When the cache expires, just run the query and store into cache again for another 30 mins. No cron required. — gus, Dec 04 '11 at 15:02

ajreal · Accepted Answer · 2011-11-26T14:41:11.590

Unfortunately, cron is better and reliable solution.

Cron is a time-based job scheduler in Unix-like computer operating systems. The name cron comes from the word "chronos", Greek for "time". Cron enables users to schedule jobs (commands or shell scripts) to run periodically at certain times or dates. It is commonly used to automate system maintenance or administration, though its general-purpose nature means that it can be used for other purposes, such as connecting to the Internet and downloading email.

If you are to store the output into disk file,
you can always check the filemtime is lesser than 30 minutes,
before proceed to re-run the expensive queries.

score 1 · Answer 2 · edited Nov 14 '13 at 15:15

1

There is nothing at all wrong with using a cron to store this kind of stuff somewhere.
If you're looking for a bit more sophisticated caching methods, I suggest reading into memcached or APC, which could both provide a solution for your problem.

edited Nov 14 '13 at 15:15

Shog9

156,901
35
231
235

answered Nov 19 '10 at 12:54

Jasper De Bruijn

1,434
7
11

Thanks im using MySQL cache so that should take of it – gus Nov 19 '10 at 12:59

score 0 · Answer 3 · answered Nov 19 '10 at 12:35

0

Cron Job is best approach nothing else i seen feasible.

answered Nov 19 '10 at 12:35

XMen

29,384
41
99
151

score 0 · Answer 4 · answered Nov 19 '10 at 12:40

You have many to do this, I think the good not the best, you can store your data on table and display it every 30 min. using the function sleep()
I recommend you to take a look at wordpress blog system, and specially at the plugin BuddyPress..

I did the same some time ago, and every time someone load the page, the query do the job and retrieve the information from database, I remenber It was something like

SELECT COUNT(*) FROM my_table

and I got the number of posts in my case.

Anyway, there are so many approach. Good Luck.

Dont forget The cron is always your best friend.

score 0 · Answer 5 · answered Nov 19 '10 at 13:11

Using cron is the simplest way to solve the problem.

One good reason for not using cron - you'll be generating the stats even if nobody will request them.

Depending on the length of time it takes to generate the data (you might want to keep track of the previous counts and just add counts where the timestamp is greater than the previous run - with appropriate indexes!) then you could trigger this when a request comes in and the data looks as if it is stale.

Note that you should keep the stats in the database and think about how to implement a mutex to avoid multiple requests trying to update the cache at the same time.

However the right solution would be to update the stats every time a record is added. Unless you've got very large traffic volumes, the overhead would be minimal. While 'SELECT count(*) FROM some_table' will run very quickly you'll obviously run into problems if you don't simply want to count all the rows in a table (e.g. if blogs and replies are held in the same table). Indeed, if you were to implement the stats update as a trigger on the relevant tables, then you wouldn't need to make any changes to your PHP code.

Most effiecient way to display some stats using PHP/MySQL

5 Answers5