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

Questions
,
1

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…

Regards

2

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.
3

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…

4

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.

5

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.