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:
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
What have I missed and how do I make it work?