[SOLVED] INSERT / SELECT

[jaymc]

I have a table which has 5 million+ rows

 

I need to normalise it into 2 tables

 

1 table to store all reduntant data which is hardly ever accessed and the other to store all the resent data

 

However, when a page is accessed it first selects the latest data, then inserts a row into the table. My idea is have it do

 

1x select from the month database

2x inserts (1 into the huge table and one into the smaller one

 

So, instead of having to select from a table with 5 mill rows, I can select from the month table which will give the most resent data

Im just wondering, is there hardly any over head when inserting, even into a HUGE table due to it not having to analyise 5 million rows...

 

Or, is there just as much overhead due to the index table having to be re wrote

[fenway]

I don't understand what you're describing... give concrete examples.

[jaymc]

I have a table, when a user accesses a page there username is inserted into the table, if it already exists, it just increases a field in the existing row

 

On the page itself, it then uses a SELECT to pull out the last 5 people to access the page

 

Aswell as this, it counts up all the rows associated with that user

 

There is 5 million rows, growing in that table so I need to better mange it

 

#####################

 

So.. I was thinking of splitting it into 2 parts, my idea is as follows

 

Create a new table, this will only stored the last 2 weeks access and will be used to pull out the last 5 visits. To take care of it counting up all accesses even though it only has the last 2 weeks, I will generate a dummy row which contains a number which reflects how many rows are in there related to the user. This prevents having to count through 5 million rows and acts kind of like a cache

 

I could even have another table actually called row_cache and just join it

 

#########################

 

Then every 2 weeks I will have a cron job move/bind all the data from the 2 week table into the master table (5 million rows)

 

 

Real pain in the arse, but perhaps has to be done and is logical to do it the way I have suggested.. Unless, and this is why Im here, there is a more logically defined way to tackle this kind of problem

 

There doesnt appear to be any serious damage at the moment, queries are within the second, but its not efficient and does have slight optimization problems regardless of the indexs due to its size. It would be better to normalise

 

It doesnt help the page is accessed 800,000 a day

 

 

Any ideas

[fenway]

Can you not archive some of this information in another table? You could always use a merge table for complete historical reporting if need be, but the "latest" report wouldn't need to have anywhere near this number of records.

[jaymc]

I cant merge as the slim table will contain duplicates of the heavy table, thus every 2 weeks rebuilding the heavy table for full reporting

[fenway]

I cant merge as the slim table will contain duplicates of the heavy table, thus every 2 weeks rebuilding the heavy table for full reporting

What I'm suggesting is that you don't need the duplicates, you can simple "archive" the old records to the old table (probably even with the ARCHIVE engine)... no need for dupes.

[jaymc]

Ok thanks for ideas. I will see what I can conjur up