Updating data once per day

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
GeXus
Forum Regular
Posts: 631
Joined: Sat Mar 11, 2006 8:59 am

Updating data once per day

Post by GeXus »

I have a DB of about 10,000 products. I want to then display a random 50 of these products per day. So at midnight I will select rand 50 to display them. How would you suggest handling this so that it only selects a new random 50 in 24 hours? or at midnight.

Here are a couple options.

1. Select 50, store in memory (memcache) until 12:00 and then select a new 50 and store again, etc.

2. Create a new table that gets the selected 50 inserted into it, then select from that 50. Have a job that runs and updates that DB at 12:00.


I think #1 would be the best bet, but I'm just wondering what you guys would suggest.

Thanks!
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

Option 2 is often more portable.
GeXus
Forum Regular
Posts: 631
Joined: Sat Mar 11, 2006 8:59 am

Post by GeXus »

feyd wrote:Option 2 is often more portable.
More portable or scalable? Not sure I understand.
GeXus
Forum Regular
Posts: 631
Joined: Sat Mar 11, 2006 8:59 am

Post by GeXus »

Just want to add... This is for a relatively high-traffic site, approx. 5mill+ page views a day, so one of my concerns about using the DB, is obviously load and speed.
User avatar
Zoxive
Forum Regular
Posts: 974
Joined: Fri Apr 01, 2005 4:37 pm
Location: Bay City, Michigan

Post by Zoxive »

GeXus wrote:Just want to add... This is for a relatively high-traffic site, approx. 5mill+ page views a day, so one of my concerns about using the DB, is obviously load and speed.
I too would put it in a separate table, then you can even go back in history (Previous Days etc).
You could use a cron job to do so.

5 Million? May i ask what the site is?
If your having that much traffic, then this already shouldn't be an ordinary server.
GeXus
Forum Regular
Posts: 631
Joined: Sat Mar 11, 2006 8:59 am

Post by GeXus »

Zoxive wrote:
GeXus wrote:Just want to add... This is for a relatively high-traffic site, approx. 5mill+ page views a day, so one of my concerns about using the DB, is obviously load and speed.
I too would put it in a separate table, then you can even go back in history (Previous Days etc).
You could use a cron job to do so.

5 Million? May i ask what the site is?
If your having that much traffic, then this already shouldn't be an ordinary server.
Hmmm.... So you don't think having a separate table would be unnecessary load? I don't have much of a need for history... although I guess I could use the history to check on when pulling randoms to make sure that it doesn't pull too many of the same again... but not too likely...

I really can't say what site it is.... sorry ;-)
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

GeXus wrote:More portable or scalable? Not sure I understand.
Both actually.
GeXus
Forum Regular
Posts: 631
Joined: Sat Mar 11, 2006 8:59 am

Post by GeXus »

Ok.... so database is the way. Now, should I just use it as a lookup? Then I will have to join on the "products" table... which is fine... but again, just thinking about performance here...


As far as scalability, with memcache, it's really easy to just toss another cache server into the mix... easier than adding to a db cluster..
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Post by califdon »

Disclaimer: I have no experience with really high activity systems. That said, I too support creating a new table each day. If the cron job is not an easy solution, you could check the date of creation of the table every time and if it is different from the current date, create a new random table. That would add to the execution time of each query, but only slightly for 99.99% of the queries, with just one query a day having to wait to create the new table. Just a thought.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

Whoa, whoa, whoa.. new table each day? I don't think that's a good idea in the slightest of ways.
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Post by califdon »

feyd wrote:Whoa, whoa, whoa.. new table each day? I don't think that's a good idea in the slightest of ways.
Ordinarily I would agree with you, feyd, but this seems to me to be an unusual situation. He's talking about a fairly large table and a very high activity. Since he wants to choose just 50 rows to use for each calendar day, I think I'd rather have the millions of queries a day access 50 rows instead of 10,000 rows. On a given day, he knows that he does not want to access 9,950 of those rows.
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post by John Cartwright »

Are we talking about truncating the temporary table and repopulating with random values or creating an entirely new table each day?
Post Reply