In our database we want to create some cache tables for speed
purposes.

There will be no relationships in this tables.
The update script will be written in PHP
It takes a while to select all the values for these tables from
several other highly populated tables.

My idea is to create a temp table, build it from a DESC command from
the original table, fill it, remove the old table by the DROP TABLE
command and rename the temp table to the original name with the RENAME
TABLE command

Does anyone have experience with this kind of action and what do you
that that are the pro's and con's?

I think that speed is one of the biggest pro's

Thanks for thinking with me.

On Wed, 5 Dec 2007 06:23:22 -0800 (PST), saspijkerman wrote:
> In our database we want to create some cache tables for speed
> purposes.
>
> There will be no relationships in this tables.
> The update script will be written in PHP
> It takes a while to select all the values for these tables from
> several other highly populated tables.
>
> My idea is to create a temp table, build it from a DESC command from
> the original table, fill it, remove the old table by the DROP TABLE
> command and rename the temp table to the original name with the RENAME
> TABLE command
>
> Does anyone have experience with this kind of action and what do you
> that that are the pro's and con's?

RENAME TABLE old_table TO backup_table, new_table TO old_table;

then worry about DROPping the backup_table. Then there will never be a
point at which old_table (the one everything accesses) doesn't exist.

--
58. If it becomes necessary to escape, I will never stop to pose dramatically
and toss off a one-liner.
--Peter Anspach's list of things to do as an Evil Overlord