MySQL Bugs: #70450: Manual does NOT list all kinds of inefficient queries over FEDERATED tables

Bug #70450	Manual does NOT list all kinds of inefficient queries over FEDERATED tables
Submitted:	27 Sep 2013 13:05	Modified:	28 Jan 2015 13:56
Reporter:	Valeriy Kravchuk	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:		OS:	Any
Assigned to:	Jon Stephens	CPU Architecture:	Any
Tags:	federated, performance

Description:
This manual page, http://dev.mysql.com/doc/refman/5.6/en/federated-usagenotes.html, says the following:

"A FEDERATED table does not support indexes per se. Because access to the table is handled remotely, it is the remote table that supports the indexes."

http://dev.mysql.com/doc/refman/5.6/en/federated-description.html says:

"The remote server processes the statement and the local server retrieves any result that the statement produces (an affected-rows count or a result set)."

But none of them explain when/for what kinds of statements index on remote table is used and when all the rows from remote table are sent instead to the server where FEDERATED table is created for further processing (not involving any indexes).

Some cases of inefficient execution are well known:

http://bugs.mysql.com/bug.php?id=26697
http://bugs.mysql.com/bug.php?id=19054
...

but still are not clearly documented and limitations of FEDERATED storage engine.

How to repeat:
Try to run different kind of queries over federated table, when remote table has proper indexes to make queries efficient.

Suggested fix:
Check what queries against remote server are really generated and document all cases when all rows are selected.

Thank you for the bug report.

Taking this one after discussing with Paul/Support.

Hi Valeriy, 

Federated was developed to demonstrate access for row lookups from remote server--it wasn't really designed to work with complex queries. That being said, what you're actually probably looking for is the point when the server decides to send WHERE conditions as part of the remote request--otherwise, all other processing of the remote data happens on the local server using temporary tables (no indexes on these). So this is what we'll try to identify in the docs.

cheers

jon.

Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly.