Why not write a simple script that scans every record and inserts them
into the proper table one at a time?
In php for example..
$query = "SELECT * \n";
$query .= "FROM `X` \n";
$result = mysql_query($query);
while(($row = mysql_fetch_array($result, MYSQL_ASSOC))){
$Values = "";
foreach($row as $TmpVar => $TmpValue){
$$TmpVar = $TmpValue;
$TmpValue = mysql_real_escape_string($TmpValue);
$Values .= "'$TmpValue',";
}
$Values = substr($Values,0,-1); // remove the last comma
$Table = sprintf("x%03d",$chunkId);
$query = "INSERT INTO `$Table` \n";
$query .= "VALUES($Values) \n";
$Iresult = mysql_query($query);
}
Knowing all of the columns could allow you to hard code the insert query
values clause and avoid the foreach loop which could make it slightly
faster. You would still need to use the mysql_real_escape_string
function on any columns that contained something other than numbers.
Jacek Becla wrote:
Hi,
Is there a way to dynamically split a big table
into n smaller tables by doing a single scan of
the table that is being split? Here is more
details:
* Suppose I have a million row MyISAM table X, with
relatively small number of columns. It has
a column "chunkId" with values between 1 and 100.
* I need to split this table into 100 in-memory
tables, essentially I need to do:
INSERT INTO X001 SELECT * FROM X WHERE chunkId=1;
INSERT INTO X002 SELECT * FROM X WHERE chunkId=2;
INSERT INTO X003 SELECT * FROM X WHERE chunkId=3;
and so on.
Based on the tests, each of these individual INSERTS
costs ~50% of a full table scan of X, even with
a clustered index on chunkId. The cost is totally
dominated by the "SELECT * FROM X WHERE chunkId=<n>"
Since we need to do such split many times, this
performance is not acceptable. It feels it should
be possible to do the split through a single scan.
Any suggestions?
thanks,
Jacek
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
