List:General Discussion« Previous MessageNext Message »
From:Chris W Date:March 28 2009 1:04pm
Subject:Re: dynamically splitting a table through one full table scan
View as plain text  
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
>
Thread
dynamically splitting a table through one full table scanJacek Becla28 Mar
  • Re: dynamically splitting a table through one full table scanChris W28 Mar
  • Re: dynamically splitting a table through one full table scankabel28 Mar
    • Re: dynamically splitting a table through one full table scanJacek Becla30 Mar