Two tables synced = faster?

I recently talked to a programmer who suggests to have two different member table synced together. He said it will cause one table to be used for registration and the other for logging in and retrieving the information and cause lower load on the database.

Is he correct or is he talking gibberish?

 

He's right. One table for users and one for sessions (currently logged in users).

 

I would like to do something similar but with an CMS site. I would like the data to get posted to one table and read from another. Then sync them every 3, 6, 12 hours or something. anyone know a good way to do it?

 

Use triggers to sync them in real time.

 

Doesn't make much sense to me.

Both tables are in the same database on the same machine?

Then regardless of splitting it into 2 tables or 1, you still do the same amount of work.

The only reason you'd split is if you had a huge number of constant registrations, but I'd assume like most sites your login function is used a lot more.

To the second point, the trigger would cause the load to occur on both tables thus undoing any benefit suggested.

 

Hi viperxx,

Yes, you could have two separate tables and you could sync them up either periodically through some bulk update process or dynamically using database triggers but they bring with them a whole heap of new issues (table locks for bulk inserts/updates, multi-phase commits on triggers).

Did your friend explain why he thought this would reduce server load because I'm not sure it does.

Petey

 

I can't see any reason you would want to do this on a single server unless you are not syncing all of the information to the other table, for security or other reasons.

Otherwise, I can see doing this is on a multi server/database setup or a cluster where you Insert to one server, and select from a different server. Otherwise a second table is just going to use up more space and using a trigger or other method to sync is going to just add overhead to the server.

 

Single server with single set load....

Say you're doing 50 queries.... Wether that's accross 1 database or 2, you're still carrying out 50 queries... which means its the same load on the server...

You could even go as far as to say that searching two tables would mean more load due to accessing multiple tables for one function...?

Theres not alot to be gained or lost from doing this... I wouldnt suggest it personally, seems like much more work to me...

Chuckun

 

Indexing and using primary keys will help speed it up.

I would define a identifier in each table to carry across with a unique key.

Thing of it as header and detail record.