I have a linked server in SQL Server 2012 (SP3) that points to a MySql 5.1.61 server on another machine.

Whatever I do, I don't seem to be able to get any where filtering to occur on the remote side.

I tried all possible combinations of the linked 'Server Options' (Collation Compatible, RPC, RPC Out, Use Remote Collation, Collation Name, Enable Promotion of Distributed Transactions for PRC), I also tried different MySql connectors for ODBC (v5.1, v5.2) and tried to put (or omit) various charset clauses in the connection string.

There is only one table I'm interested in, I'm only doing selects, there are proper indices on that table for any query I might want to make, all queries execute instantly when run directly on the MySql machine, but all of them result in this execution plan in SQL Server:

enter image description here

The table contains millions of rows and it is extremely slow.

The only thing that works for me in this situation is dynamic openquery, but that needs to be in a stored procedure, and I would very much like to expose this functionality as a view.

If that matters, the MySql database charset is latin1 (ISO 8859-1 West European) and collation is latin1_swedish_ci.

What have I missed and how do I make it work?

  • If this was a SQL Server remote, I would say that the security context for the Linked Server connection did not have sufficient permission to analyse the query against the linked server to determine a good plan. Does the execution plan show an estimated row count for the Remote Scan? – Mister Magoo May 17 '16 at 23:13
  • @MisterMagoo Yes, I saw this too. Just for the sake of it, I made the linked server connect under a mysql account with all permissions assigned, that did not change anything. The row estimate is 10000, I assume this is a hardcoded guess figure. – GSerg May 18 '16 at 6:39

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.