2

When using the NodeJS mysql2 library, I am trying to understand the differences between Connection.execute and Connection.query.

As I understand it, query prepares the statement locally and then makes one call to the database. execute sends the query without parameters to the database to be prepared then sends the parameters separately to be executed with the prepared statement.

Where my understanding really falls apart is how or if the statement gets cached when using Connection.execute. Does calling Connection.execute twice in a row with the same query have any benefit?

In function B1 below, the statement gets re-used, which gives a performance improvement. However in function B2, does the statement get re-used, or are there four separate network calls being made to the database? In the case there is no re-use of the statement in B2, would B3 be the fastest option?

async function B1(connection) { const statement = await connection.prepare("SELECT 1 + ? + ?"); const result1 = await statement.execute([1, 2]); const result2 = await statement.execute([3, 4]); await statement.close(); return [result1, result2]; } async function B2(connection) { const result1 = await connection.execute("SELECT 1 + ? + ?", [1, 2]); const result2 = await connection.execute("SELECT 1 + ? + ?", [3, 4]); return [result1, result2]; } async function B3(connection) { const result1 = await connection.query("SELECT 1 + ? + ?", [1, 2]); const result2 = await connection.query("SELECT 1 + ? + ?", [3, 4]); return [result1, result2]; }
Improve this question

3 Answers 3

Reset to default
1

Where my understanding really falls apart is how or if the statement gets cached when using Connection.execute. Does calling Connection.execute twice in a row with the same query have any benefit?

According to the documentation it does:

MySQL2 provides execute helper which will prepare and query the statement. You can also manually prepare / unprepare statement with prepare / unprepare methods.

The example they give is

import mysql from 'mysql2/promise'; try { // create the connection to database const connection = await mysql.createConnection({ host: 'localhost', user: 'root', database: 'test', }); // execute will internally call prepare and query const [results, fields] = await connection.execute( 'SELECT * FROM `table` WHERE `name` = ? AND `age` > ?', ['Rick C-137', 53] ); console.log(results); // results contains rows returned by server console.log(fields); // fields contains extra meta data about results, if available } catch (err) { console.log(err); }

and the tip

If you execute same statement again, it will be picked from a LRU cache which will save query preparation time and give better performance.

LRU cache: Cache replacement algorithms are efficiently designed to replace the cache when the space is full. The Least Recently Used (LRU) is one of those algorithms. As the name suggests when the cache memory is full, LRU picks the data that is least recently used and removes it in order to make space for the new data. The priority of the data in the cache changes according to the need of that data i.e. if some data is fetched or updated recently then the priority of that data would be changed and assigned to the highest priority , and the priority of the data decreases if it remains unused operations after operations.

So it is cached and eventually be forgotten if the cache is full and it is the least recent entry, but until then it will be reusable.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

0

Your, understanding mostly correct but just clarify:

connection.query(sql,value):

  • The query string is sent directly to the MySQL server.
  • Values are interpolated locally before sending the full query to MySQL.
  • Each call is independent; MySQL does not cache or reuse queries.
  • Not protected from SQL injection unless manually handled properly.

connection.execute(sql,value):

  • MySQL itself prepares the statement (not just locally).
  • It sends the query structure first and then parameters separately.
  • It prevents SQL injection.
  • MySQL caches the prepared statement for reuse within the session.

So, B1 function is the most efficient when running the same query multiple times because connection.prepare sends a single query to MySQL to create a prepared statement and also close after execute query.

B2 function is wasteful because it prepares the statement twice.

B3 function is simple but less efficient than B1 function for repeated queries.

Improve this answer

1 Comment

"B2 function is wasteful" it turns out is incorrect. I used Wireshark to look into exactly what was happening, and B2 only prepares the statement once, same as B1. I suspect this is because of the local LRU cache.
0

You're on the right track with your understanding of the differences between Connection.execute and Connection.query in the mysql2 library.

Key Differences Between execute and query:

  • query: This method prepares the SQL statement locally and sends it as-is to the database for execution. It supports interpolation with parameters directly.
  • execute: This method uses prepared statements. When you use execute, the SQL query is sent to the database first for preparation, and then the parameters are sent separately to be executed with the pre-prepared statement. The database often caches these prepared statements to optimize performance for subsequent executions with the same query structure.

Does execute benefit from statement reuse?

Prepared statements can be cached and reused by the MySQL server. When you use Connection.execute, the query is sent for preparation on the database side, and if the same query (with the same structure) is executed again, the server may reuse the prepared statement from its internal cache, leading to performance benefits.

B1 vs. B2:

  • B1 (connection.prepare + statement.execute):

    • Here, you explicitly prepare the statement first using connection.prepare. Once prepared, you execute it multiple times with different parameters using statement.execute.
    • Statement Reuse: The prepared statement is cached by the database, and when you execute it multiple times (like with result1 and result2), the statement itself is reused for those queries, which avoids the overhead of preparing the query again. This can improve performance, especially for repeated queries.

  • B2 (connection.execute):

    • Here, connection.execute sends the SQL query to the database, and the query is prepared on the fly each time.
    • Statement Reuse: In this case, since you're calling connection.execute twice with the same query, the database may cache the prepared statement internally, but there's still an overhead of sending the query to be prepared each time. So while there's some benefit in terms of caching, it may not be as significant as in B1 where you explicitly reuse the prepared statement.

    However, the MySQL server will likely cache the prepared statement after the first execution in B2, meaning the overhead of preparing the query for the second execution will be reduced.

  • B3 (connection.query):

    • This method sends the query to the database each time with the parameters. It's similar to connection.execute but without explicitly preparing the query on the database side.
    • No Prepared Statement: There is no reuse of a prepared statement, so every query is fully processed by the database, which can be less efficient if you're executing the same query multiple times with different parameters.

Performance Comparison:

  • B1 is likely the most performant in cases where the same query is being executed multiple times with different parameters because you explicitly reuse the prepared statement (statement.execute).
  • B2 can also be performant, but it involves preparing the statement on the database side each time you call execute, even though prepared statements may be cached by the MySQL server.
  • B3 is the least performant for repeated queries with different parameters because it doesn't benefit from prepared statement reuse, and each query is sent to the database as a fresh query.

Conclusion:

  • B1 should be the fastest, especially when executing the same query multiple times with different parameters, because of the explicit statement preparation and reuse.
  • B2 could still perform well due to server-side prepared statement caching, but it's not as efficient as B1 because it doesn't explicitly reuse the prepared statement on the client side.
  • B3 is the least efficient for repeated queries with parameters due to the lack of prepared statement reuse.

In summary, B1 would typically offer the best performance for repeated queries with parameters because it maximizes statement reuse both on the client side and the database side.

Improve this answer

1 Comment

Just a warning to future users, this was AI generated.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.