LIMIT and TOP clauses with @Mysql not forwarded as server-side filtering

Good morning,

It seems that the LIMIT clause with a @Mysql container is not forwarded to the server and is rather done client side:

select * from mytable@mysql LIMIT 10

Similarly , the TOP clause seems not forwarded:

Select TOP 10 * from mytable@mysql

This could create bad things when the table is big…


Yes, that is correct. limit and top (when applied before sorting) are not forwarded. It is not expected that this will provide a generic performance advantage.

The SQL-engine uses streaming data where possible, so when the source can stream rows, the SQL-engine stops retrieving data from MySQL in this case after 10 rows.

Most platforms provide streaming data themselves. The noticeable exceptions are:

  • Twinfield transactions: all transaction data of a company in one large payload.
  • PostgreSQL (or more accurately: npgsql): provides streaming data but for PostgreSQL-internal reasons we fail to understand the whole cursor is retrieved silently into the client.

ok thanks.

on my case, it seems that streaming does not work on the Mysql server either due to some version or options on the source server.

I figured out this because my statement with SELECT * from table@mysql LIMIT 10 takes 37s to show the results, and the same time is required to execute SELECT * from table@mysql.

not to say that this mysql server shut down the TCP connection after 37 seconds… so SELECT * from table@mysql gives me partial results…

It seems that the default MySQL driver does not support streaming data. There is little to no documentation, but given that a member of the MySQL development team now seems involved with the PostgreSQL driver with a similar limitation, there is little chance for it. The Java-driver seems to have limited capabilities for this, but has the same limitation as the PostgreSQL-driver that it needs to load and discard all rows silently to reflect internal working of the database engine.

It is recommended to use a native call to limit the data on MySQL, to use a view with filters or use elements such as a where-clause that can be forwarded to the server.

This question was automatically closed after at least 2 weeks of inactivity after a possible solution was provided. The last answer given has been marked as a solution.

Please ask a new question via a separate topic if the problem occurs again. Please include a link to this topic in the new question by pasting its URL into the text.