»Benchmark: mysql, mysqli - Buffer, Cache und Prepared Stmt

/*
// vars
$id = 1111;
$email = 'info@example.org';
$note = "\x00sadipscing\x00 \x1aelitr\x1a, sed diam nonumy. \nnamet\n, \rconsetetur\r Lorem 'dolor' ipsum \sit\\";
// db
$db = mysql_connect('localhost', 'user', 'pw') OR exit();
mysql_select_db('db');
$db = new mysqli('localhost', 'user', 'pw', 'db') OR exit();
// with cache:
// SQL_CACHE
// without cache:
// SQL_NO_CACHE
// with buffer:
// mysql: mysql_query
// mysqli: MYSQLI_STORE_RESULT
// mysqli ps: use $stmt->store_result()
// without buffer:
// mysql: mysql_unbuffered_query
// mysqli: MYSQLI_USE_RESULT
// mysqli ps: don't use $stmt->store_result()

######### SQL_CACHE #########

// mysql select with $query, sprintf and SQL_CACHE
// run: 0.202927241327
// run: 0.20274058342
// fastest run: 0.17111992836
$query = sprintf(
"SELECT SQL_CACHE *
FROM emails
WHERE id = %d
AND email = '%s'
AND note = '%s'
LIMIT 100",
intval($id),
mysql_real_escape_string($email),
mysql_real_escape_string($note)
);
if (!($result = mysql_query($query))) exit();
while ($row = mysql_fetch_assoc($result)) echo('');
mysql_free_result($result);

// mysql select with sprintf and SQL_CACHE
// run: 0.202936003208
// run: 0.202630057341
// fastest run: 0.198309898376
if (!($result = mysql_query(sprintf(
"SELECT SQL_CACHE *
FROM emails
WHERE id = %d
AND email = '%s'
AND note = '%s'
LIMIT 100",
intval($id),
mysql_real_escape_string($email),
mysql_real_escape_string($note)
)))) exit();
while ($row = mysql_fetch_assoc($result)) echo('');
mysql_free_result($result);

// mysql select with $query and SQL_CACHE
// run: 0.203236801625
// run: 0.203072795869
// fastest run: 0.194211006165
$query =
"SELECT SQL_CACHE *
FROM emails
WHERE id = " . intval($id) . "
AND email = '" . mysql_real_escape_string($email) . "'
AND note = '" . mysql_real_escape_string($note) . "'
LIMIT 100";
if (!($result = mysql_query($query))) exit();
while ($row = mysql_fetch_assoc($result)) echo('');
mysql_free_result($result);

// mysql select with SQL_CACHE
// run: 0.203366568087
// run: 0.203140323161
// fastest run: 0.200508832932
if (!($result = mysql_query(
"SELECT SQL_CACHE *
FROM emails
WHERE id = " . intval($id) . "
AND email = '" . mysql_real_escape_string($email) . "'
AND note = '" . mysql_real_escape_string($note) . "'
LIMIT 100"
))) exit();
while ($row = mysql_fetch_assoc($result)) echo('');
mysql_free_result($result);

// mysql select with $query, sprintf, mysql_unbuffered_query and SQL_CACHE
// run: 0.169497578144
// run: 0.168652458192
// fastest run: 0.166500806808
$query = sprintf(
"SELECT SQL_CACHE *
FROM emails
WHERE id = %d
AND email = '%s'
AND note = '%s'
LIMIT 100",
intval($id),
mysql_real_escape_string($email),
mysql_real_escape_string($note)
);
if (!($result = mysql_unbuffered_query($query))) exit();
while ($row = mysql_fetch_assoc($result)) echo('');
mysql_free_result($result);

// mysql select with sprintf, mysql_unbuffered_query and SQL_CACHE
// run: 0.169313673974
// run: 0.168320553303
// fastest run: 0.165802001953
if (!($result = mysql_unbuffered_query(sprintf(
"SELECT SQL_CACHE *
FROM emails
WHERE id = %d
AND email = '%s'
AND note = '%s'
LIMIT 100",
intval($id),
mysql_real_escape_string($email),
mysql_real_escape_string($note)
)))) exit();
while ($row = mysql_fetch_assoc($result)) echo('');
mysql_free_result($result);

// mysqli select with $query, sprintf, SQL_CACHE and MYSQLI_STORE_RESULT
// run: 0.203386082652
// run: 0.203128581048
// fastest run: 0.197916984558
$query = sprintf(
"SELECT SQL_CACHE *
FROM emails
WHERE id = %d
AND email = '%s'
AND note = '%s'
LIMIT 100",
intval($id),
$db->real_escape_string($email),
$db->real_escape_string($note)
);
if (!($result = $db->query($query))) exit();
while ($row = $result->fetch_assoc()) echo('');
$result->close();

// mysqli select with sprintf, SQL_CACHE and MYSQLI_STORE_RESULT
// run: 0.204141454701
// run: 0.203148534297
// fastest run: 0.201350927353
if (!($result = $db->query(sprintf(
"SELECT SQL_CACHE *
FROM emails
WHERE id = %d
AND email = '%s'
AND note = '%s'
LIMIT 100",
intval($id),
$db->real_escape_string($email),
$db->real_escape_string($note)
)))) exit();
while ($row = $result->fetch_assoc()) echo('');
$result->close();

// mysqli select with $query, SQL_CACHE and MYSQLI_STORE_RESULT
// run: 0.203663911817
// run: 0.205211720468
// fastest run: 0.19709610939
$query =
"SELECT SQL_CACHE *
FROM emails
WHERE id = " . intval($id) . "
AND email = '" . $db->real_escape_string($email) . "'
AND note = '" . $db->real_escape_string($note) . "'
LIMIT 100";
if (!($result = $db->query($query))) exit();
while ($row = $result->fetch_assoc()) echo('');
$result->close();

// mysqli select with SQL_CACHE and MYSQLI_STORE_RESULT
// run: 0.203260421758
// run: 0.203011736868
// fastest run: 0.19740986824
if (!($result = $db->query(
"SELECT SQL_CACHE *
FROM emails
WHERE id = " . intval($id) . "
AND email = '" . $db->real_escape_string($email) . "'
AND note = '" . $db->real_escape_string($note) . "'
LIMIT 100"
))) exit();
while ($row = $result->fetch_assoc()) echo('');
$result->close();

// mysqli select with $query, sprintf, SQL_CACHE and MYSQLI_USE_RESULT
// run: 0.169879922866
// run: 0.171357295512
// fastest run: 0.166729927063
$query = sprintf(
"SELECT SQL_CACHE *
FROM emails
WHERE id = %d
AND email = '%s'
AND note = '%s'
LIMIT 100",
intval($id),
$db->real_escape_string($email),
$db->real_escape_string($note)
);
if (!($result = $db->query($query, MYSQLI_USE_RESULT))) exit();
while ($row = $result->fetch_assoc()) echo('');
$result->close();

// mysqli select with sprintf, SQL_CACHE and MYSQLI_USE_RESULT
// run: 0.169101116657
// run: 0.169385228156
// fastest run: 0.165516138077
if (!($result = $db->query(sprintf(
"SELECT SQL_CACHE *
FROM emails
WHERE id = %d
AND email = '%s'
AND note = '%s'
LIMIT 100",
intval($id),
$db->real_escape_string($email),
$db->real_escape_string($note)
), MYSQLI_USE_RESULT))) exit();
while ($row = $result->fetch_assoc()) echo('');
$result->close();

// mysqli select with $query, SQL_CACHE and MYSQLI_USE_RESULT
// run: 0.172444839478
// run: 0.171396617889
// fastest run: 0.156838178635
$query =
"SELECT SQL_CACHE *
FROM emails
WHERE id = " . intval($id) . "
AND email = '" . $db->real_escape_string($email) . "'
AND note = '" . $db->real_escape_string($note) . "'
LIMIT 100";
if (!($result = $db->query($query, MYSQLI_USE_RESULT))) exit();
while ($row = $result->fetch_assoc()) echo('');
$result->close();

// mysqli select with SQL_CACHE and MYSQLI_USE_RESULT
// run: 0.173195414545
// run: 0.172171146867
// fastest run: 0.167582035065
if (!($result = $db->query(
"SELECT SQL_CACHE *
FROM emails
WHERE id = " . intval($id) . "
AND email = '" . $db->real_escape_string($email) . "'
AND note = '" . $db->real_escape_string($note) . "'
LIMIT 100"
, MYSQLI_USE_RESULT))) exit();
while ($row = $result->fetch_assoc()) echo('');
$result->close();

// mysqli select with prepared statement, dynamic fields and SQL_CACHE
// run: 0.313724272253
// run: 0.313278152942
// fastest run: 0.301989078522
if ($stmt = $db->prepare(
"SELECT SQL_CACHE *
FROM emails
WHERE id = ?
AND email = ?
AND note = ?
LIMIT 100"
)) {
$stmt->bind_param('iss', $id, $email, $note);
$stmt->execute();
$meta = $stmt->result_metadata();
while ($field = $meta->fetch_field()) {
$row[] = &$fields[$field->name];
}
$meta->close();
call_user_func_array(array($stmt, 'bind_result'), $fields);
$i = 0;
while ($stmt->fetch()) {
foreach ($fields as $k => $v) {
$data[$i][$k] = $v;
echo('');
}
$i++;
}
$stmt->close();
}

// mysqli select with prepared statement, fixed fields and SQL_CACHE
// run: 0.278509860038
// run: 0.274894304277
// fastest run: 0.259500980377
if ($stmt = $db->prepare(
"SELECT SQL_CACHE *
FROM emails
WHERE id = ?
AND email = ?
AND note = ?
LIMIT 100"
)) {
$stmt->bind_param('iss', $id, $email, $note);
$stmt->execute();
$stmt->bind_result($row['id'], $row['email'], $row['note']);
while ($stmt->fetch()) echo('');
$stmt->close();
}

// mysqli select with prepared statement outside of the loop, fix fields and SQL_CACHE
// run: 0.208601517682
// run: 0.20873098612
// fastest run: 0.200495004654
$stmt = $db->prepare(
"SELECT SQL_CACHE *
FROM emails
WHERE id = ?
AND email = ?
AND note = ?
LIMIT 100"
);
//loop begins here to be able to change WHERE parms
$stmt->bind_param('iss', $id, $email, $note);
$stmt->execute();
$stmt->bind_result($row['id'], $row['email'], $row['note']);
while ($stmt->fetch()) echo('');

// mysqli select with prepared statement outside of the loop, fix fields, store_result and SQL_CACHE
// run: 0.242778592114
// fastest run: 0.23714017868

// mysqli select with prepared statement, SQL_CACHE and store_result
// will be slower than with fix fields

######### SQL_NO_CACHE #########

// mysql select with $query, sprintf and SQL_NO_CACHE
// run: 0.250889410975
// fastest run: 0.249036073685
$query = sprintf(
"SELECT SQL_NO_CACHE *
FROM emails
WHERE id = %d
AND email = '%s'
AND note = '%s'
LIMIT 100",
intval($id),
mysql_real_escape_string($email),
mysql_real_escape_string($note)
);
if (!($result = mysql_query($query))) exit();
while ($row = mysql_fetch_assoc($result)) echo('');
mysql_free_result($result);

// mysql select with sprintf and SQL_NO_CACHE
// will be slower than with SQL_CACHE

// mysql select with $query and SQL_NO_CACHE
// will be slower than with SQL_CACHE

// mysql select with SQL_NO_CACHE
// will be slower than with SQL_CACHE

// mysqli select with $query, sprintf, SQL_NO_CACHE and MYSQLI_STORE_RESULT
// run: 0.251598188879
// fastest run: 0.246910095215
$query = sprintf(
"SELECT SQL_NO_CACHE *
FROM emails
WHERE id = %d
AND email = '%s'
AND note = '%s'
LIMIT 100",
intval($id),
$db->real_escape_string($email),
$db->real_escape_string($note)
);
if (!($result = $db->query($query))) exit();
while ($row = $result->fetch_assoc()) echo('');
$result->close();

// mysqli select with sprintf, SQL_NO_CACHE and MYSQLI_STORE_RESULT
// will be slower than with SQL_CACHE

// mysqli select with $query, SQL_NO_CACHE and MYSQLI_STORE_RESULT
// will be slower than with SQL_CACHE

// mysqli select with SQL_NO_CACHE and MYSQLI_STORE_RESULT
// will be slower than with SQL_CACHE

// mysqli select with $query, sprintf, SQL_NO_CACHE and MYSQLI_USE_RESULT
// run: 0.214941697124
// fastest run: 0.211448907852
$query = sprintf(
"SELECT SQL_NO_CACHE *
FROM emails
WHERE id = %d
AND email = '%s'
AND note = '%s'
LIMIT 100",
intval($id),
$db->real_escape_string($email),
$db->real_escape_string($note)
);
if (!($result = $db->query($query, MYSQLI_USE_RESULT))) exit();
while ($row = $result->fetch_assoc()) echo('');
$result->close();

// mysqli select with sprintf, SQL_NO_CACHE and MYSQLI_USE_RESULT
// will be slower than with SQL_CACHE

// mysqli select with $query, SQL_NO_CACHE and MYSQLI_USE_RESULT
// will be slower than with SQL_CACHE

// mysqli select with SQL_NO_CACHE and MYSQLI_USE_RESULT
// will be slower than with SQL_CACHE

// mysqli select with prepared statement, dynamic fields and SQL_NO_CACHE
// run: 0.313881859775
// fastest run: 0.29983997345
if ($stmt = $db->prepare(
"SELECT SQL_NO_CACHE *
FROM emails
WHERE id = ?
AND email = ?
AND note = ?
LIMIT 100"
)) {
$stmt->bind_param('iss', $id, $email, $note);
$stmt->execute();
$meta = $stmt->result_metadata();
while ($field = $meta->fetch_field()) {
$row[] = &$fields[$field->name];
}
$meta->close();
call_user_func_array(array($stmt, 'bind_result'), $fields);
$i = 0;
while ($stmt->fetch()) {
foreach ($fields as $k => $v) {
$data[$i][$k] = $v;
echo('');
}
$i++;
}
$stmt->close();
}

// mysqli select with prepared statement, fixed fields and SQL_NO_CACHE
// run: 0.275153286459
// fastest run: 0.256268024445
if ($stmt = $db->prepare(
"SELECT SQL_NO_CACHE *
FROM emails
WHERE id = ?
AND email = ?
AND note = ?
LIMIT 100"
)) {
$stmt->bind_param('iss', $id, $email, $note);
$stmt->execute();
$stmt->bind_result($row['id'], $row['email'], $row['note']);
while ($stmt->fetch()) echo('');
$stmt->close();
}

// mysqli select with prepared statement outside of the loop, fix fields and SQL_NO_CACHE
// run: 0.208914251327
// run: 0.208983807565
// fastest run: 0.198924064636
$stmt = $db->prepare(
"SELECT SQL_NO_CACHE *
FROM emails
WHERE id = ?
AND email = ?
AND note = ?
LIMIT 100"
);
//loop begins here to be able to change WHERE parms
$stmt->bind_param('iss', $id, $email, $note);
$stmt->execute();
$stmt->bind_result($row['id'], $row['email'], $row['note']);
while ($stmt->fetch()) echo('');

// mysqli select with prepared statement outside of the loop, fix fields, store_result and SQL_NO_CACHE
// run: 0.242572655678
// fastest run: 0.233605146408

// mysqli select with prepared statement, SQL_NO_CACHE and store_result
// will be slower than with fix fields
*/