SQL Query progress using PostgreSQL or My SQL

Question

Introduction

Hello everyone. Working on web application management I often find myself having a problem that I have never solved with my current knowledge. In my applications, most data extraction processes are long-lasting due to complex queries and large amounts of data. In fact, the waiting time of a data extraction through PHP is largely spent by the QUERY EXECUTION (in most cases). Suppose we have this common situation

Question

At this point my question is: Is there a solution for know at what point of query execution process has arrived and then do a query trace for have my [PROGRESS QUERY %]?

My solution

So far the solution I have used are this: "Query time history"

When I run a data extraction query with specific parameters i save in the table the duration of the query and every time that query is executed with those parameters i overwrite the rescheduled duration with the average of all durations. So I can have an estimate based on an average, obviously ignoring other parameters that can affect the duration of a query and i can call a client side function based on seconds estimated and populate the [PROGRESS QUERY %].

Example: (i use boostrap progress bar for view)

HTML

<!-- where "data-seconds" are the average seconds of the execution saved in my query time history table -->
<button type="button" id="runQuery" data-seconds="500">Get Data</button>    
<div class="progress">
  <div class="progress-bar" role="progressbar" aria-valuenow="0" aria-valuemin="0" aria-valuemax="100" style="width:0%">
  </div>
</div>

JAVASCRIPT

$("#runQuery").on("click", function() {
    var currentSeconds = 0;
    var totalSeconds = parseFloat($(this).data("seconds"));
    var $progressBar = $(".progress-bar");
    var progressPercentage = 0;
    var execution;

    $.ajax({
        url: "scriptForQueryExecution.php", type: "POST", beforeSend: function() {
            execution = setInterval(function() {
                progressPercentage = currentSeconds / totalSeconds * 100;
                $progressBar.css("width", progressPercentage + '%').attr("aria-valuenow", progressPercentage);

                currentSeconds++;
            }, 1000);
        }, success: function() {
            $progressBar.css("width", '100%').attr("aria-valuenow", 100);
            clearInterval(execution);
        }
    });
});

Answer 1

MariaDB supports "progress reporting" within their process lists, through SHOW PROCESSLIST and INFORMATION_SCHEMA.PROCESSLIST which update progress information with an 5-second interval (default). Details can be found here: https://mariadb.com/kb/en/library/progress-reporting/.

PostgreSQL supports progress reporting too, albeit only for the VACUUM command: https://www.postgresql.org/docs/9.6/static/progress-reporting.html

All you need to do is query this info with separate XHR requests, and animate your progress indicator with CSS animations.

Answer 2

Sure you've probably thought of this, but you could write a query that determines the number of rows in the table using select count([ID]) from dbo.MyTable which should take less that 1 second and then build in some calculation on the client side that calculates the current number of rows divided by the total number of rows in the table...