SELECT within range or set active = 0 if out of range

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
User avatar
Luke
The Ninja Space Mod
Posts: 6424
Joined: Fri Aug 05, 2005 1:53 pm
Location: Paradise, CA

SELECT within range or set active = 0 if out of range

Post by Luke »

We're setting up a job listing application (ReverandDexter & I). In the application you can set when your listing should expire. Does it make more sense to SET active = 0 if expiration date > NOW() or does it make more sense to just add a WHERE expire_date < NOW() to all of my select queries?
User avatar
superdezign
DevNet Master
Posts: 4135
Joined: Sat Jan 20, 2007 11:06 pm

Post by superdezign »

The WHERE clause is faster.

But if you plan to delete the expired listings, I'd go for the first method.
User avatar
Luke
The Ninja Space Mod
Posts: 6424
Joined: Fri Aug 05, 2005 1:53 pm
Location: Paradise, CA

Post by Luke »

We plan on just not showing the posts if they are expired. I don't think that we plan on deleting them at any time.
User avatar
ReverendDexter
Forum Contributor
Posts: 193
Joined: Tue May 29, 2007 1:26 pm
Location: Chico, CA

Post by ReverendDexter »

For a little more insight into the debate, I'm a staunch advocate of the "setting active = 0" strategy. Using that, a user will never run into the situation where their job post is "active", but not displaying. Also, it makes it very easy for a user to get their job back on the board (just reactivate it) when it comes off (if they so choose to relist).

I agree that the where clause method is much faster, I just don't agree that it's *better*.
User avatar
Luke
The Ninja Space Mod
Posts: 6424
Joined: Fri Aug 05, 2005 1:53 pm
Location: Paradise, CA

Post by Luke »

and I'm not sure either way... hence the thread LOL
Post Reply