Re: How to make small tables from big table? (SELECT INTO doesn't seem to work)

[Fran Lee]

Peter, thanks for your response.

Apparently mysql does not support SELECT INTO so here is the solution we came up with...

CREATE TABLE tbl_stuff01 LIKE tbl_stuff;
INSERT INTO tbl_stuff01 SELECT * FROM tbl_stuff LIMIT 5 OFFSET 0;


this is more or less like your suggestion except CREATE TABLE LIKE creates the new table with all the details of the original table except for the actual data... and we can write a PHP loop to do this for all the smaller tables we will need.


and, fyi...

Peter,

The "LIMIT 5" was only used to make the example simple but I think you mixed up the definitions of OFFSET and LIMIT... see below.

LIMIT 5 limits the number of records selected from tbl_stuff to 5
OFFSET 0 tells it to start with the first record

LIMIT 2500 is what we will actually use (chunking 40k table to 16 2.5k tables)
OFFSET will be incremented by 2500 on each PHP loop along with the filename.
to produce the 16 new smaller tables.

Then we will dump those and import them to our online server (as it chokes if we try to import a table longer than 2500 records)

Then we will do the opposite on the online server and create the big table from the 16 smaller table.

Yea, we should get a different web host but other than this little nuance the service has been good and the price has been right :-)