how to use mysql temporal cache tables

Question

I took this example from a book where visitors_today is the cache table from visitor_stored. The idea is to store all the visitors per day (visitors_stored) using a cache table.

Create table visitors_today(
today int unsigned not null default 0,
vcount bigint unsigned not null default 0
)
engine = Innodb;

create table visitors_stored(
v_count bigint unsigned not null default 0,
vcount_date DATE not null primary key
)
engine=MyIsam;

My questions:

• Do I need to set up something?
• A cache table is it just a regular table, the difference is the data will be delete after update the visitors_stored?, or just because the storage engine it will be faster?

Answer 1

If I understand your question correctly, your examples are just storing the vcount of visitors (visitors_stored) from today.

This means

1. You'll want a script to reset the visitors_today each day at midnight.

2. You'll want a trigger on visitors_stored to update the visitors_today table.

Why would you do it? If you regularly wanted to know how many visitors were from today, you would do a

SELECT vcount FROM visitors_today

instead of

SELECT v_count FROM visitors_stored WHERE vcount_date=NOW(); -- Probably wouldn't use NOW, but an actual static date!

It depends on how your server is tuned, but generally if you have an index on the vcount_date column in visitors_stored, it will suffice. If your visitors_stored table was overly large (>100GB say), the cache table might be of use.

If you start to see performance issues with computed columns, a cache column or table might be the way to go.