I have a MySQL server linked in SQL Server and I can query it like this:

select * from [MYSQL]...[table] where [column] = 1

this works, but if the table contains many rows performance drops, even if column is indexed in MySQL.

I understand that generally speaking a distributed query cannot make use of remote indexes and optimizations, while OPENQUERY does, but here I cannot use OPENQUERY since queries are generated from a JavaEE JPA application.

Question is: are there any OLE DB Providers for MySQL which can take advantage of remote indexes, or is it just not supported at all by distributed queries?

  • Is the problem that the remote table has a lot of rows, or that the remote table has a lot of rows where column = 1? – Aaron Bertrand Feb 20 '17 at 16:41
  • The remote table has a lot of rows, say 60M rows, and just one row with column = 1, the distributed query takes forever while on MySQL just a second. The distributed query is clearly not using MySQL indexes, so I wonder if there are OLE DB Providers which are able to use them to optimize the query. – Giovanni Lovato Feb 20 '17 at 17:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.