CODEWITHSUNDEEP
phpmysqlmysqli
KodornaRocks
KodornaRocks

Reputation: 405

PHP Multiple Selects Simultaneously?

Question first, explanation later.

In PHP, is there any way to send multiple SQL selects at the same time, and let mysql process all of the selects at the same time?

Because waiting for all the responses separately is taking too long.

What do I need in the code?

I have this:

$sqlStringArray[0=>"SELECT a,b,c,d,e,f FROM T1 JOIN (SELECT x... WHERE) AS RandomJoin WHERE... ORDER BY... GROUP BY....",
            1=>"SELECT x,y FROM T2 WHERE...",
            2=>"SELECT random FROM randomTable WHERE...";

I use something like this:

/* This is not good enough, each select could take 2 seconds */
foreach ($sqlStringArray as $key => $value) {
    $mysqli->query($value);
}

I want something like this:

$resultSet = $mysqli->executeAllSelectsAtTheSameTimeAsynchronous($sqlStringArray);

(Not necessary to read below)

I'm sory about my english, hope it's enough for understanding.

(Reason) I Have a performance monitoring system that grows really fast (160.000 filterable items, 280 counters each, 4 records/hour each counter), around 4.300.800.000 new records/day.

The UI allows users to select which counters they want to view, daterange, formulas (e.g Sum 2 counters, subtract for that other, filter by region/name/type/anything)..

Everything works well, but some users selects 120+ different kinds of formulas, and every formula will be given back to a JSON data that will be processed by highcharts to be displayed at user interface, every select is taking around 2~5 seconds (I believe I did a really great job at the select part, I don't think I could improve it because there's no way I could summarize that data once every user uses his own formulas, and they look randomly into the network [actually only look when they have user complaints]).

I would like to know if there are any ways that I can send a "select list" in order to have mysql handle everything at once and give back a bunch of arrays.

@MyWay solved it, below some tests (Filename self explains), I used the same queries in all of the files, so the more I run, the faster it goes dont know why, but the difference is so huge that nevermind, at least 8 times faster.

testusr@garbageServer:/dev/shm/test/20151209$ php teste_diogo_sequential.php
Memory spike:
not real: 0.22097778320312 MiB
real: 0.25 MiB

Total execution time in seconds: 87.4132

testusr@garbageServer:/dev/shm/test/20151209$ php teste_diogo_multiquery.php
Memory spike:
not real: 0.22097778320312 MiB
real: 0.25 MiB

Total execution time in seconds: 11.1795

testusr@garbageServer:/dev/shm/test/20151209$ php teste_diogo_multiquery_JSON.php
Memory spike:
not real: 0.22097778320312 MiB
real: 0.25 MiB

Total execution time in seconds: 6.9231

testusr@garbageServer:/dev/shm/test/20151209$ php teste_diogo_sequential.php
Memory spike:
not real: 0.22097778320312 MiB
real: 0.25 MiB

Total execution time in seconds: 62.1036

Upvotes: 1

Views: 196

Answers (2)

Thamilhan
Thamilhan

Reputation: 13313

Use PHP multi_query

<?php
$conn = mysqli_connect("localhost", "username", "password", "db");
$query  = "SELECT a,b,c,d,e,f FROM T1 JOIN (SELECT x... WHERE) AS RandomJoin WHERE... ORDER BY... GROUP BY....;";
$query .= "SELECT x,y FROM T2 WHERE...;";
$query .= "SELECT random FROM randomTable WHERE...";
if (mysqli_multi_query($conn, $query)) {
    do {
        if ($result = mysqli_store_result($conn)) {
            while ($row = mysqli_fetch_row($result)) {
                printf("%s\n", $row[0]);
            }
            mysqli_free_result($result);
        }
    } while (mysqli_next_result($conn));
}

mysqli_close($conn);
?>

Another SO question for efficiency between multi_query and query

Upvotes: 2

Amitesh Kumar
Amitesh Kumar

Reputation: 3079

try This it will help you to use multiple select : 

 <?php
    $con=mysqli_connect("localhost","my_user","my_password","my_db");
    // Check connection
    if (mysqli_connect_errno())
      {
      echo "Failed to connect to MySQL: " . mysqli_connect_error();
      }

    $sql = "SELECT Lastname FROM Persons ORDER BY LastName;";
    $sql .= "SELECT Country FROM Customers";

    // Execute multi query
    if (mysqli_multi_query($con,$sql))
    {
      do
        {
        // Store first result set
        if ($result=mysqli_store_result($con)) {
          // Fetch one and one row
          while ($row=mysqli_fetch_row($result))
            {
            printf("%s\n",$row[0]);
            }
          // Free result set
          mysqli_free_result($result);
          }
        }
      while (mysqli_next_result($con));
    }

    mysqli_close($con);
    ?>

Upvotes: 1

Related Questions