Query vs Execute

[nzlotrfan]

Hey there,

I'm just wanting to understand what is the best practice.

1. My understanding is that using a pool over just opening and closing connections, is more efficient. And 2) I also understand that using prepared statements (in conjunction with pool.excute) is more efficient than using pool.query. Is that right?

I started to wonder because I noticed that SELECT * FROM user WHERE user_id = ? works with both pool.query and pool.execute. So my assumption is that I should just use pool.execute for all of my queries?

Thank you

[sidorares]

1 - yes, main reason to reuse connection is to reduce query/execute latency, especially in case of non-local mysql server. Each connection is more than one packet round trip and adds to a total query execution

2 - it depends, .execute() under the hood consist of 2 steps: prepare ( only for the first time .execute is used with this connection ) and then statement.execute. For short query that is already in the mysql server query cache performance might be identical ( or sometimes even better for .query() ). The main reason to use .execute is to avoid client side parameters serialization into sql string and potential security bugs if this serialization is not safe ( .execute does not need it as it sends query first for preparation and parameters list on each subsequent .execute()

should just use pool.execute for all of my queries?

If you have parameters ( SELECT * FROM user WHERE user_id = ? example ) then suggestion is yes by default, and only use .query when hitting prepared statement syntax limitations ( for example, sql IN statement )

[nzlotrfan]

Thanks very much for your reply.
Can I just confirm one more thing?

Using say pool.query with a query of: SELECT * FROM user WHERE user_id = ${req.body.userId} I know is bad practice due to SQL injection etc. But do I understand right that using pool.execute with the above query would be acceptable and that execute will take care of the preventing SQL injections)? (I'm just trying to make sense of your mentioning: .execute does not need it as it sends query first for preparation and parameters list on each subsequent .execute())

Sorry I'm still a bit of a novice with regards to mysql2. Thanks!

[sidorares]

.execute is not trying to parse your query, the main point is that you as author separate code ( sql ) from the data ( parameters )
With .query() the api is similar but query and parameters are concatenated into single text string before sending to the server. With .execute quey and parameters are aways sent separately ( query text via COM_PREPARE command once on a first .execute call for that sql, parameters via COM_EXECUTE command on each .execute() call )

[nzlotrfan]

Thank you very much :)