dongxie3352
2013-05-07 23:59
浏览 56
已采纳

MySQLi查询vs PHP数组,哪个更快?

I'm developing an algorithm for intense calculations on multiple huge arrays. Right now I have used PHP arrays to do the job but, it seems slower than what I needed it to be. I was thinking on using MySQLi tables and convert the php arrays into database rows and then start the calculations to solve the speed issue.

At the very first step, when I was converting a 20*10 PHP array into 200 rows of database containing zeros, it took a long time. Here is the code: (Basically the following code is generating a zero matrix, if you're interested to know)

$stmt = $mysqli->prepare("INSERT INTO `table` (`Row`, `Col`, `Value`) VALUES (?, ?, '0')"); 
for($i=0;$i<$rowsNo;$i++){
    for($j=0;$j<$colsNo;$j++){
        //$myArray[$j]=array_fill(0,$colsNo,0);
        $stmt->bind_param("ii", $i, $j); 
        $stmt->execute(); 
    }
}
$stmt->close();

The commented-out line "$myArray[$j]=array_fill(0,$colsNo,0);" would generate the array very fast while filling out the table in next two lines, took a very longer time.

Array time: 0.00068 seconds

MySQLi time: 25.76 seconds

There is a lot more calculating remaining and I got worried even after modifying numerous parts it may get worse. I searched a lot but I couldn't find any answer on whether the array is a better choice or mysql tables? Has anybody done or know about any benchmarking test on this?

I really appreciate any help.

Thanks in advance


UPDATE:

I did the following test for a 273*273 matrix. I created two versions for the same data. First one, a two-dimension PHP array and the second one, a table with 273*273=74529 rows, both containing the same data. The followings are the speed test results for retrieving similar data from both [in here, finding out which column(s) of a certain row has a value equal to 1 - the other columns are zero]:

  • It took 0.00021 seconds for the array.
  • It took 0.0026 seconds for mysqli table. (more than 10 times slower)

My conclusion is sticking to the arrays instead of converting them into database tables.

Last thing to say, in case the mentioned data is stored in the database table in the first place, generating an array and then using it would be much much slower as shown below (slower due to data retrieval from database):

  • It took 0.9 seconds for the array. (more than 400 times slower)
  • It took 0.0021 seconds for mysqli table.
  • 写回答
  • 好问题 提建议
  • 追加酬金
  • 关注问题
  • 邀请回答

3条回答 默认 最新

相关推荐 更多相似问题