saving stocks quotes in mysql database too slow

Question

At the moment i'm trying to save stock data from loading csv files of yahoo. I created a database called stocks with that constitutes the ticker names of NASDAQ/NYSE/AMEX as tables ( about 6,5 K tables). the problem ist the saving of the data i get is taking way too long. I want to load an save the datas in 1 minute ticks. the loading takes me about 30 seconds and the saving about 3 mins. i have to reduce the saving time. here is the script i wrote for geting and saving the data:

 include('mysql_connect.php');

 $ticker=array();
 $db="stocks";
 $res=mysql_query("SHOW TABLES FROM $db");
 while($row=mysql_fetch_array($res,MYSQL_NUM)){
    $ticker[]=$row[0];
 }

 $ticker_length=count($ticker);
 $steps=floor($ticker_length/200);

for($j=0;$j<=$steps;$j++){
    $ticker_url="";
    if($j<$steps){
        for($i=$j*200;$i<($j+1)*200;$i++){
            if($i==(($j+1)*200)-1){
                $ticker_url=$ticker_url.$ticker[$i];
            }else{
                $ticker_url=$ticker_url.$ticker[$i]."+";
            }
        }

    $url="http://finance.yahoo.com/d/quotes.csv?s='$ticker_url'&f=snxab2l1va2p2opm3m4ghd1t1=.csv";
    $filehandle=fopen("$url","r");
    while(!feof($filehandle)){

        $line=fgetcsv($filehandle,1024);
        if( $line[0]=="" || $line[0]==null || !isset($line[0]) ){
            //echo"Leer<br/>";
        }else{
            $Symbol=strtolower($line[0]);
            $Name=$line[1];
            $LastTradePriceOnly=$line[5];
            $Volume=$line[6];


                    $query=mysql_query("INSERT INTO $Symbol(symbol,Name,LastTradePriceOnly,Volume) 
                    VALUES('$Symbol','$Name','$LastTradePriceOnly','$Volume')");
        }

     }
     fclose($filehandle);   

    }else{
        for($i=$j*200;$i<$ticker_length;$i++){
            if($i==$ticker_length-1){
                $ticker_url=$ticker_url.$ticker[$i];
            }else{
                $ticker_url=$ticker_url.$ticker[$i]."+";
            }
        }

    $url="http://finance.yahoo.com/d/quotes.csv?s='$ticker_url'&f=snxab2l1va2p2opm3m4ghd1t1=.csv";
    $filehandle=fopen("$url","r");
    while(!feof($filehandle)){

        $line=fgetcsv($filehandle,1024);
        if( $line[0]=="" || $line[0]==null || !isset($line[0]) ){
            //echo"empty<br/>";
        }else{
            $Symbol=strtolower($line[0]);
            $Name=$line[1];
            $LastTradePriceOnly=$line[5];
            $Volume=$line[6];

                    $query=mysql_query("INSERT INTO $Symbol(symbol,Name,LastTradePriceOnly,Volume) 
                    VALUES('$Symbol','$Name','$LastTradePriceOnly','$Volume')");
        }

     }
     fclose($filehandle);
    }
}

so now i want to know:

Is there a way of saving faster by changing the script?
I know that there is the possibility of configuring mysql settings like buffer_size etc. How i can do that and what i have got to change?
if this the saving time i require i not possible with mysql, what alternatives are out there?

would be pleased by getting some help.

thanks.

1 table for each ticker name? 6.5K tables? That's insane. Why don't you store everything to a single table, containing a ticker_name column? And to insert in batch like this, you should definitely use prepared statements. See http://php.net/manual/en/mysqli.quickstart.prepared-statements.php — JB Nizet, Dec 30 '12 at 12:26
This will be slow. you have thousands table and your are inserting rows in a loop! — Shiplu Mokaddim, Dec 30 '12 at 12:33
i wanted to put the ticks of the stocks in one table so everything is better organized... thought retrieving the data would be slow if everything goes in one table. Im not that warm with mysql. i will read sth abaout the prepared statements. thanks for the little crashcourse. — sami_analyst, Dec 30 '12 at 15:59

score 0 · Accepted Answer · answered Jan 06 '13 at 01:00

i have found the solution. Mysql allows you to save your lines and cols from csv without reading the lines in complex loops:

    $url = "http://finance.yahoo.com/d/quotes.csv?s=AAPL+MSFT+GOOG&f=...the datas you need=.csv";

    $sql = "LOAD DATA LOCAL INFILE '" . $url . "' 
    INTO TABLE `" . $data_tablename . "` 
    FIELDS 
    TERMINATED BY ',' 
    OPTIONALLY ENCLOSED BY '\"' 
    LINES 
    TERMINATED BY '\n' 
    (`your_cols`,``,.....)";

     mysql_query($sql)or die(mysql_error());

my script took me 250 sec on the server but now just 45 sec's. Amazing profit of time....

saving stocks quotes in mysql database too slow

1 Answers1