Can PHP issue multiple processed mysql queries in Apache environment?

Question

I have one database server with 4 cores and a web server with PHP. I want one PHP script to be able to send queries to the database server so that they run in parallel on the database server, one for each mysqld process. Usually in PHP you do this:

$sql = new mysqli( [insert connection parameters] ); $sql->query( "SELECT 'Complex Query A'" ); $sql->query( "SELECT 'Complex Query B'" ); $sql->query( "SELECT 'Complex Query C'" ); $sql->query( "SELECT 'Complex Query D'" ); 

But they run in serial and use only one mysqld process. In this application, each query (A to D) processes a different piece of data, but works on the same InnoDB table set.

One possible solution is to make apache's AJAX calls to break it into sub-scripts that can be executed in parallel, but I assume that Apache will process these ajax calls in sequence with one httpd process per client.

Is there any way to achieve this? Does anyone have any experience with mysqlnd MYSQLI_ASYNC functions? Can they work in parallel with one database server and mysqli connection?

Purpose: we run real-time analytical tools that generate graphs, and I would like to use the processing power in our database to speed up queries that take time.

Answer 1

What you need is to execute asynchronous requests. Perhaps starting in PHP 5.3, when you have a new mysqlnd driver, then you can pass the MYSQLI_ASYNC flag to the query() method. Script execution will continue without waiting for the completion of your request. The PHP manual has a good example of using comments for the poll() method.

You will need a separate MySQL connection for each query that you want to execute in parallel.