MySQL: Can providers leverage CLIENT_OPTIONAL_RESULTSET_METADATA?

[divega]

As commented by @bgrainger on email:

we'd want to use MySQL Server 8.0 with the new CLIENT_OPTIONAL_RESULTSET_METADATA flag (https://dev.mysql.com/doc/refman/8.0/en/c-api-optional-metadata.html, https://dev.mysql.com/worklog/task/?id=8134) to dramatically reduce MySQL protocol bandwidth usage from sending unnecessary column metadata. This would require the "direct" API, because the client would need knowledge of the DB schema to be able to deserialise the bytes off the wire correctly.

Adding this to our list of issues here so that we can discuss it later with @bgrainger or other MySQL experts as possible optimizations. FWIW, @roji mentioned that this (the ability to execute queries without retrieving unnecessary column metadata) is one of the advantages of using prepared statements in PostgreSQL.

[roji]

Just to provide more detail on @divega's words above: yes, one of the advantages of a good implementation of DbCommand.Prepare() is that all necessary resultset metadata (and anything else necessary for executing the query and interpreting its result) is exchanged at preparation time, making actual execution as slim as possible. In the PostgreSQL case this includes both the resultset metadata and the actual SQL, which does not need to be transferred on every execution.

[bgrainger]

Prepared statements in MySQL use a (theoretically) more efficient binary format on the wire, but still send all the column metadata every time the prepared statement is executed: https://dev.mysql.com/doc/internals/en/binary-protocol-resultset.html

[bgrainger]

I wrote a prototype implementation of client-side support for this feature (that supports the RawDb.ReadSingleRow use case): https://github.com/mysql-net/MySqlConnector/compare/e79e8193a645a68e63b088a0c4912cbaaf70fd21...bgrainger:optional-result-set-metadata?expand=1

The results are not encouraging (I suspect they are identical within experimental inaccuracy):

_	Docker		Windows
Before	36.49s	5481.0 req/s	17.27s	46,327.9 req/s
After	36.07s	5545.1 req/s	17.20s	46,514.3 req/s

I'm putting this case aside for now; can revisit when MySQL Server 8.0 is the standard benchmark DB.