Is there anyway to check the execution time(elapsed or remaining) for the "SELECT... INTO OUTFILE" in php mysql?

Question

Is there anyway to check the execution time(elapsed or remaining) for the "SELECT... INTO OUTFILE" in php mysql?

212 Views Asked by vikram At 19 April 2025 at 01:11

I am writing a query, which fetches many millions of rows from a table and puts in to a txt file.

$sQuery  = " ( ";
        $sQuery .= " SELECT number ";
        $sQuery .= " INTO OUTFILE '".$outputfile_dnd."' ";
        $sQuery .= " FIELDS TERMINATED BY ','  ";   
        $sQuery .= " LINES TERMINATED BY '\r\n' ";
        $sQuery .= " FROM `".$database."`.$temp_table_name";
        $sQuery .= " WHERE number IN (SELECT number FROM `db_dnd`.`dnd` )";
        $sQuery .= ")";

Since there will be huge data, this will take several minutes of time to complete sometimes. I want to make the users to know how much more time the process will run(roughly). I am thinking to keep a button in frontend, when user clicks the button, it should show the time remaining. If its not possible to findout remaining time, If i can get the elapsed time and number of rows selected,then also its fine. Any help? Worst case I am thinking to go with INSERT IN TO other table then put to file after everything is inserted.

Original Q&A

There are 1 best solutions below

**Kirsty Wright** · Accepted Answer

You can run an ajax request to the php script when the user presses the button. Then you can count in Javascript, the time until you get a response from the ajax file.

To get the time elapsed in php you can do (the following assumes you are using mysqli):

$startTime = microtime();
$result = $mysqli->query($sQuery); #This is your function that runs the actual query
$endTime= microtime(true);
$timeTaken= $endTime- $startTime;

To get the number of rows (in mysqli) you can just grab it off of the result object.

$numRows = $result->num_rows;

Edit for Js ajax version :

So if you have Jquery on your frontend the following would work. So this involves separating all of your php that runs and displays the query into a separate file. Then call the URL of that file below and it will show how long has elapsed before the output of said file.

<script src="https://code.jquery.com/jquery-3.1.1.min.js" integrity="sha256-hVVnYaiADRTO2PzUGmuLJr8BLUSjGIZsDYGmIJLv2b8=" crossorigin="anonymous"></script>
<script type="text/javascript">
$(document).ready(function(){
    var startTime = new Date().getTime();
    var requestInterval = window.setInterval(function() {
        $("#content_area").html("Time Elapsed :"+(new Date().getTime() - startTime) / 1000+" Seconds");
    }, 500);
    jQuery.get('URLofYOURScriptHERE', 
        function(data, status, xhr) {
            clearInterval(requestInterval)
            var timeTaken = new Date().getTime() - startTime;
            $("#content_area").html(data);
        }
    );
});
</script>
<body>
<div id="content_area">
</div>
</body>

Is there anyway to check the execution time(elapsed or remaining) for the "SELECT... INTO OUTFILE" in php mysql?

There are 1 best solutions below

Related Questions in PHP

Related Questions in MYSQL

Related Questions in INSERT

Related Questions in SELECT-INTO-OUTFILE

Trending Questions

Popular # Hahtags

Popular Questions