Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?
share|improve this question
add comment

1 Answer

up vote 3 down vote accepted

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.

share|improve this answer
    
thanks for your answer, but my question is, how to use a cache table, how is it difference of a regular table? –  jcho360 May 14 '12 at 14:24
    
It's all in the table definition. It's just a normal table that you query on. –  Derek Downey May 14 '12 at 14:37
    
so you are saying that cache tables it's just a regular table where you place the data TEMPORALLY, before to save it into the BIG table and then delete the cache table? –  jcho360 May 14 '12 at 15:45
    
I'm saying as far as MySQL is concerned, based on your DDL, there is no difference in your 'cache' table and a 'BIG' table. The difference lays in how your application uses them. –  Derek Downey May 14 '12 at 15:48
    
ohh I got it!, THANKS A LOT. –  jcho360 May 14 '12 at 15:50
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.