CODEWITHSUNDEEP
mysqldatabasetime
lisak
lisak

Reputation: 21981

Database entries existence depends on time

I have this situation here.

I'm not a database expert, just a user. What is the best way to implement this scenario ?

Finally I have to move old order entries to a different backup table. My point is make the table lightweight, because there could be many requests per second. I'm not sure how much load does it put on the database, whether it there is a difference between criterion like boolean and time for searching

Upvotes: 0

Views: 125

Answers (2)

Larry Lustig
Larry Lustig

Reputation: 50990

It sounds like you're going to an awful lot of work to solve a problem there's no evidence that you have. Especially if you're going to be archiving older records off, why don't you make sure that you can't do the time-based searches before you create an entire system dedicated to maintaining a second column that performs the same function as the time column.

If you do decide that you need to keep a separate list of active auctions consider doing it not with a special column (and largely wasted index) but rather by creating an active_auctions table that contains only the primary key column(s) from auctions. Add a row to this table for each auction that becomes active and delete it when it becomes inactive. JOIN this table to the main auction table for a list of currently active auctions.

Upvotes: 1

Jonathan Leffler
Jonathan Leffler

Reputation: 754170

I think your second bullet point is dubious:

There are a lot of orders so it is better to have a field "active" instead of listing them based on time queries.

It probably is better to have an end time for the 'order' and then query for active orders by looking for current time before end time. The active field will give you lousy selectivity if you index it (a bitmap index might be better). Additionally, you have to update the rows to change them from active to inactive - and you could do things with active orders past their deadline because the active flag did not get changed in time.

  1. Yes, you would have to poll for active flags that should be inactive - using precious DBMS query bandwidth.
  2. No, it can't change the field automatically.
  3. The UPDATE necessary is worse than just selecting.
  4. Yes, you need something like an end timestamp.

Moving old orders to secondary storage (something other than the main table, not necessarily outside the DBMS, let alone on tape) is quite probably a good idea. It is also not time critical; you can do it at any time after the order has ceased to be interesting.

Upvotes: 1

Related Questions