How to kill a MySQL query with Node.js without disconnecting?

Question

Context: I'm building a web application that calls data from a large db (several millions of rows for table); sometimes a user can change his mind and call for new data before the query on the db has been completed.

Technical question: I tried to kill the query in this cases using:

app.get("/data", function(req, res) {
    if (req.query.killQuery == "true") {
        con.query("KILL \"" + threadId + "\"", function(err) {
            if (err) throw err;
            console.log("I have interrupted the executing query for a new request");
            giveData(req, res); //The function that will execute a new query
        }); 
        return;
    }
    giveData(req, res); //The function that will execute a new query
});

Now I have several doubts about this code:

• I had to use a second connection to kill the thread of the first, since the first was unable to perform new queries before the first was completed. Is this a Node.js behaviour or is it the right way to do this kind of things?

• The KILL thread_id statement closes the whole connection instead of stopping the single query. Again, is it Node.js behaviour, or is it MySQL itself? Should I really disconnect and reconnect to stop a query and start with an other?

Answer 1

If you have a modern version of MySQL, you can use KILL QUERY <threadId> instead which will only kill the currently executing query on that connection but leave the connection intact.