Menu
Introduction to the PHP fetch() method The fetch() method allows you to fetch a row from a result set associated with a PDOStatement object. Internally, the fetch() method fetches a single row from a result set and moves the internal pointer to the next row in the result set.
PDO::FETCH_BOTH (default) Returns an array indexed by both column name and 0-indexed column number as returned in your result set.
I wanted to know the answer to this question, so I wrote a quick benchmark script.
I suggest you run this benchmark on your own server, however, this is a typical result on my setup, for single row results:
For large data sets, these results are typical:
See the comments on git hub, fetching all the rows does change the test.
The benchmark script posted in another answer is counting prepare() and execute() to the time so I write a script for testing only fetch time. I have MySQL database, with about 100000 rows of real data. LongRow contains TEXT column (about 4kB of data per row). ShortRow is (30 bytes per row). FETCH_INTO uses predefined class with all column properties. PHP version 5.4. MySQL 5.5.35. One thing is missing here: average of some runs.
Array
(
[Items: 10] => Array
(
[ShortRow] => Array
(
[FETCH_INTO] => 0.0001068115234375
[FETCH_OBJECT] => 0.00013899803161621
[FETCH_COLUMN (STR8)] => 6.0081481933594E-5
[FETCH_COLUMN (INT)] => 5.8174133300781E-5
[FETCH_NUM] => 9.2029571533203E-5
[FETCH_ASSOC] => 9.8943710327148E-5
[FETCH_BOTH] => 0.00011897087097168
[FETCH_LAZY] => 6.3180923461914E-5
)
[LongRow] => Array
(
[FETCH_INTO] => 0.00012779235839844
[FETCH_OBJECT] => 0.00016498565673828
[FETCH_COLUMN (TEXT)] => 4.9829483032227E-5
[FETCH_COLUMN (INT)] => 4.3153762817383E-5
[FETCH_NUM] => 0.00010180473327637
[FETCH_ASSOC] => 0.00010895729064941
[FETCH_BOTH] => 0.00013399124145508
[FETCH_LAZY] => 4.3869018554688E-5
)
)
[Items: 100] => Array
(
[ShortRow] => Array
(
[FETCH_INTO] => 0.00081610679626465
[FETCH_OBJECT] => 0.0011789798736572
[FETCH_COLUMN (STR8)] => 0.00040292739868164
[FETCH_COLUMN (INT)] => 0.00041294097900391
[FETCH_NUM] => 0.00067806243896484
[FETCH_ASSOC] => 0.00076103210449219
[FETCH_BOTH] => 0.00092482566833496
[FETCH_LAZY] => 0.00043201446533203
)
[LongRow] => Array
(
[FETCH_INTO] => 0.0010471343994141
[FETCH_OBJECT] => 0.0013670921325684
[FETCH_COLUMN (TEXT)] => 0.00037693977355957
[FETCH_COLUMN (INT)] => 0.00030612945556641
[FETCH_NUM] => 0.00079894065856934
[FETCH_ASSOC] => 0.00094914436340332
[FETCH_BOTH] => 0.0011270046234131
[FETCH_LAZY] => 0.00031089782714844
)
)
[Items: 1000] => Array
(
[ShortRow] => Array
(
[FETCH_INTO] => 0.0082287788391113
[FETCH_OBJECT] => 0.0099248886108398
[FETCH_COLUMN (STR8)] => 0.0037147998809814
[FETCH_COLUMN (INT)] => 0.0038070678710938
[FETCH_NUM] => 0.006443977355957
[FETCH_ASSOC] => 0.0070838928222656
[FETCH_BOTH] => 0.008652925491333
[FETCH_LAZY] => 0.0039060115814209
)
[LongRow] => Array
(
[FETCH_INTO] => 0.0092909336090088
[FETCH_OBJECT] => 0.011745929718018
[FETCH_COLUMN (TEXT)] => 0.0031650066375732
[FETCH_COLUMN (INT)] => 0.0025970935821533
[FETCH_NUM] => 0.0068809986114502
[FETCH_ASSOC] => 0.0087978839874268
[FETCH_BOTH] => 0.010183811187744
[FETCH_LAZY] => 0.0026650428771973
)
)
[Items: 10000] => Array
(
[ShortRow] => Array
(
[FETCH_INTO] => 0.067224025726318
[FETCH_OBJECT] => 0.086459159851074
[FETCH_COLUMN (STR8)] => 0.03191089630127
[FETCH_COLUMN (INT)] => 0.031462907791138
[FETCH_NUM] => 0.047988891601562
[FETCH_ASSOC] => 0.05333399772644
[FETCH_BOTH] => 0.065713882446289
[FETCH_LAZY] => 0.028834819793701
)
[LongRow] => Array
(
[FETCH_INTO] => 0.12389183044434
[FETCH_OBJECT] => 0.15812706947327
[FETCH_COLUMN (TEXT)] => 0.03816294670105
[FETCH_COLUMN (INT)] => 0.035914897918701
[FETCH_NUM] => 0.1117901802063
[FETCH_ASSOC] => 0.10923099517822
[FETCH_BOTH] => 0.12394094467163
[FETCH_LAZY] => 0.030914068222046
)
)
)
Here is a code too:
//Code is missing connect to DB
header('Content-Type: text/plain');
class testModel1 {
public $id;
public $invoice;
public $transaction;
public $creditedInvoice;
public $amount;
public $payment_type;
public $currency;
public $created;
public $timestamp;
}
class testModel2 {
public $id;
public $cid;
public $c_amount;
public $object;
public $person;
public $date;
public $type;
public $invoice_type;
public $version;
public $templateInvoice;
public $account;
public $variable_symbol;
public $number;
public $accounting_year;
public $amount;
public $currency;
public $comment;
public $data; //is a text column (avg size about 4kB)
public $created;
public $modified;
public $timestamp;
}
$items = array(10,100,1000,10000);
foreach($items as $item) {
$ivStmt = $pdo->prepare("SELECT * FROM `invoices_paying` LIMIT $item");
$ivStmt->execute(array('items'=>$item));
$out = array();
$testModel1 = new testModel1();
$ivStmt->setFetchMode(PDO::FETCH_INTO, $testModel1);
$start = microtime(true);
while($id = $ivStmt->fetch()) {
}
$end = microtime(true);
$out['FETCH_INTO'] = $end-$start;
$ivStmt->execute(array('items'=>$item));
$start = microtime(true);
while($id = $ivStmt->fetchObject()) {
}
$end = microtime(true);
$out['FETCH_OBJECT'] = $end-$start;
$ivStmt->execute(array('items'=>$item));
$start = microtime(true);
while($id = $ivStmt->fetchColumn(5)) {
}
$end = microtime(true);
$out['FETCH_COLUMN (STR8)'] = $end-$start;
$ivStmt->execute(array('items'=>$item));
$start = microtime(true);
while($id = $ivStmt->fetchColumn(0)) {
}
$end = microtime(true);
$out['FETCH_COLUMN (INT)'] = $end-$start;
$ivStmt->execute(array('items'=>$item));
$start = microtime(true);
while($id = $ivStmt->fetch(PDO::FETCH_NUM)) {
}
$end = microtime(true);
$out['FETCH_NUM'] = $end-$start;
$ivStmt->execute(array('items'=>$item));
$start = microtime(true);
while($id = $ivStmt->fetch(PDO::FETCH_ASSOC)) {
}
$end = microtime(true);
$out['FETCH_ASSOC'] = $end-$start;
$ivStmt->execute(array('items'=>$item));
$start = microtime(true);
while($id = $ivStmt->fetch(PDO::FETCH_BOTH)) {
}
$end = microtime(true);
$out['FETCH_BOTH'] = $end-$start;
$ivStmt->execute(array('items'=>$item));
$start = microtime(true);
while($id = $ivStmt->fetch(PDO::FETCH_LAZY)) {
}
$end = microtime(true);
$out['FETCH_LAZY'] = $end-$start;
$table['Items: '.$item]['ShortRow'] = $out;
}
foreach($items as $item) {
$ivStmt = $pdo->prepare("SELECT * FROM `invoices` LIMIT $item");
$ivStmt->execute(array('items'=>$item));
$out = array();
$testModel2 = new testModel2();
$ivStmt->setFetchMode(PDO::FETCH_INTO, $testModel2);
$start = microtime(true);
while($id = $ivStmt->fetch()) {
}
$end = microtime(true);
$out['FETCH_INTO'] = $end-$start;
$ivStmt->execute(array('items'=>$item));
$start = microtime(true);
while($id = $ivStmt->fetchObject()) {
}
$end = microtime(true);
$out['FETCH_OBJECT'] = $end-$start;
$ivStmt->execute(array('items'=>$item));
$start = microtime(true);
while($id = $ivStmt->fetchColumn(17)) {
}
$end = microtime(true);
$out['FETCH_COLUMN (TEXT)'] = $end-$start;
$ivStmt->execute(array('items'=>$item));
$start = microtime(true);
while($id = $ivStmt->fetchColumn(0)) {
}
$end = microtime(true);
$out['FETCH_COLUMN (INT)'] = $end-$start;
$ivStmt->execute(array('items'=>$item));
$start = microtime(true);
while($id = $ivStmt->fetch(PDO::FETCH_NUM)) {
}
$end = microtime(true);
$out['FETCH_NUM'] = $end-$start;
$ivStmt->execute(array('items'=>$item));
$start = microtime(true);
while($id = $ivStmt->fetch(PDO::FETCH_ASSOC)) {
}
$end = microtime(true);
$out['FETCH_ASSOC'] = $end-$start;
$ivStmt->execute(array('items'=>$item));
$start = microtime(true);
while($id = $ivStmt->fetch(PDO::FETCH_BOTH)) {
}
$end = microtime(true);
$out['FETCH_BOTH'] = $end-$start;
$ivStmt->execute(array('items'=>$item));
$start = microtime(true);
while($id = $ivStmt->fetch(PDO::FETCH_LAZY)) {
}
$end = microtime(true);
$out['FETCH_LAZY'] = $end-$start;
$table['Items: '.$item]['LongRow'] = $out;
}
print_r($table);
I am wondering if FETCH ASSOC is faster when I'm going to fetch a lot of data; or they are the same?
Do not fetch a lot of data, when you don't need it. that's all.
Once you need it - microscopic difference between these methods would be your least concern.
ASSOC, BOTH and OBJ are generally the same, except that they return a different structure. No performance differences there.
LAZY does some sort of lazy loading. PDO::FETCH_LAZY creates the object variable names as they are accessed. This means that you get the performance penalty only when you access the properties, not when calling fetch(). This is useful if you use only a part of the returned data.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With