You can use the following code to execute, for example, 10 simultaneous queries:
$query = "SELECT `field1`, `field2` FROM `table` WHERE `field1`='something'";
$all_links = array();
for($i=0; $i<10; $i++) {
$link = mysqli_connect("your.mysql.server.here","your@user","pa$$w0rd",DataBase_Name");
$link->query($query, MYSQLI_ASYNC);
$all_links[] = $link;
}
$processed = 0;
do {
$links = $errors = $reject = array();
foreach ($all_links as $link) {
$links[] = $errors[] = $reject[] = $link;
}
if (!mysqli_poll($links, $errors, $reject, 1)) {
continue;
}
foreach ($links as $link) {
if ($result = $link->reap_async_query()) {
print_r($result->fetch_row());
if (is_object($result))
mysqli_free_result($result);
} else die(sprintf("MySQLi Error: %s", mysqli_error($link)));
$processed++;
}
} while ($processed < count($all_links));
NOTE: If you get a "Warning: mysqli::query() expects parameter 2 to be long, string given" you have a mysqlnd installation or configuration issue.
mysqli::poll
mysqli_poll
(PHP 5 >= 5.3.0, PHP 7)
mysqli::poll -- mysqli_poll — Poll connections
설명
객체 기반 형식
public static int mysqli::poll
( array
&$read
, array &$error
, array &$reject
, int $sec
[, int $usec
] )절차식 형식
int mysqli_poll
( array
&$read
, array &$error
, array &$reject
, int $sec
[, int $usec
] )Poll connections. mysqlnd에서만 사용할 수 있습니다. The method can be used as static.
인수
-
read -
List of connections to check for outstanding results that can be read.
-
error -
List of connections on which an error occured, for example, query failure or lost connection.
-
reject -
List of connections rejected because no asynchronous query has been run on for which the function could poll results.
-
sec -
Number of seconds to wait, must be non-negative.
-
usec -
Number of microseconds to wait, must be non-negative.
반환값
Returns number of ready connections upon success, FALSE otherwise.
예제
Example #1 A mysqli_poll() example
<?php
$link1 = mysqli_connect();
$link1->query("SELECT 'test'", MYSQLI_ASYNC);
$all_links = array($link1);
$processed = 0;
do {
$links = $errors = $reject = array();
foreach ($all_links as $link) {
$links[] = $errors[] = $reject[] = $link;
}
if (!mysqli_poll($links, $errors, $reject, 1)) {
continue;
}
foreach ($links as $link) {
if ($result = $link->reap_async_query()) {
print_r($result->fetch_row());
if (is_object($result))
mysqli_free_result($result);
} else die(sprintf("MySQLi Error: %s", mysqli_error($link)));
$processed++;
}
} while ($processed < count($all_links));
?>
위 예제의 출력:
Array
(
[0] => test
)
참고
- mysqli_query() - Performs a query on the database
- mysqli_reap_async_query() - Get result from async query
add a note
User Contributed Notes 4 notes
l_sanczyk at hotmail dot com ¶
9 years ago
shestero at meta dot ua ¶
8 years ago
Sometime it's not clear what does it mean as connection is "ready" . Does it mean the query completed or just some records ready to read?
Is it possible to use asynchronous (unblocking) and unbuffered SELECT-query? I.e. with both MYSQLI_ASYNC|MYSQLI_USE_RESULT
I want some poll-like code inside my loop that has four case-options:
1. Poll wait time-out is over but no records of result set is ready.
2. One or more records are ready to read (but the query still running).
3. Query is successfully over (completed; no more records).
4. Error.
Is it possible?
As I understood the operation of reading a record from unbuffered query is blocking if there are no ready records at the moment, and there is no functions to get out how much records are ready ?
ekalashnikov at gmail dot com ¶
6 years ago
Improved version of using polling to check for slow query,
"KILL #ID" does not work if id of the myqsli link is the same as the one that kills it,
so you have to disconnect from mysqli and connect again with new link to
kill the #ID.
<?php
// Slow SQL Query
$SelectSql = 'SELECT * FROM SLOW_QUERY';
$link = mysqli_connect('localhost','user','pass','database');
mysqli_query($SelectSql, MYSQLI_ASYNC);
$thread_id = mysqli_thread_id($link);
// Ignore user abort so we can kill the query
ignore_user_abort(true);
$MaxTime = 5; // seconds
$Overtime = false;
$StartTime = time();
do
{
// Poll MySQL
$links = $errors = $reject = array($link);
$poll = mysqli_poll($links, $errors, $reject, 0, 500000);
// Check if the connection is aborted and the query was killed
if (connection_aborted()) {
$link_new = mysqli_connect('localhost','user','pass','database');
mysqli_kill($link_new, $thread_id);
$kill = mysqli_kill($link_new, $thread_id);
if ($kill)
{
die();
}
}
$EndTime = time();
// Check overtime, kill if detected overtime
if ($EndTime - $StartTime > $MaxTime)
{
$link_new = mysqli_connect('localhost','user','pass','database');
mysqli_kill($link_new, $thread_id);
$Overtime = true;
echo 'Error: Query took over '.$Overtime.'.';
}
} while (!$poll && $Overtime == false);
?>
ekalashnikov at gmail dot com ¶
6 years ago
This is how you can test and terminate slow query with polling.
<?php
// Slow SQL Query
$SelectSql = 'SELECT * FROM SLOW_QUERY';
$link = mysqli_connect('localhost','user','pass','database');
mysqli_query($SelectSql, MYSQLI_ASYNC);
$thread_id = mysqli_thread_id($link);
// Ignore user abort so we can kill the query
ignore_user_abort(true);
$MaxTime = 5; // seconds
$Overtime = false;
$StartTime = time();
do
{
// Poll MySQL
$links = $errors = $reject = array($link);
$poll = mysqli_poll($links, $errors, $reject, 0, 500000);
// Check if the connection is aborted and the query was killed
if (connection_aborted() && mysqli_kill($link, $thread_id)) {
die();
}
$EndTime = time();
// Check overtime, kill if detected overtime
if ($EndTime - $StartTime > $MaxTime)
{
mysqli_kill($link, $thread_id);
$Overtime = true;
echo 'Error: Query took over '.$Overtime.'.';
}
} while (!$poll && $Overtime == false);
?>
