Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-5698

Using ORDER BY in a FederatedX table is abnormally slow

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Critical
    • Resolution: Fixed
    • 5.5.35
    • 5.5.36
    • None
    • Debian Wheezy AMD64

    Description

      Querying a FederatedX table with an ORDER BY clause will take about 20 to 40 times longer with MariaDB (tested on 5.5.35) than with MySQL (tested on 5.5.22 and 5.5.30), no matter if the "host" table is hosted on a MariaDB (tested with 5.5.31 and 5.5.35) or MySQL (tested with 5.5.22 and 5.5.27) server.

      I did tests with servers on the same physical network and over the Internet, results are similar in both cases.

      Steps to reproduce :

      • On the "host" server, we create the "host" table :

        CREATE TABLE `testtable` (
          `id` int(10) unsigned NOT NULL,
          PRIMARY KEY (`id`)
        ) ENGINE=InnoDB DEFAULT CHARSET=latin1

      • Fill the table with about 50k rows.
      • On the "guest(s)" server(s), we create the Federated table :

        CREATE TABLE `distanttable` (
          `id` int(10) unsigned NOT NULL,
          PRIMARY KEY (`id`)
        ) ENGINE=FEDERATED DEFAULT CHARSET=latin1 CONNECTION='mysql://testuser:testpass@HOST/testbase/testtable'

      • Check how long takes a "SELECT id FROM distanttable"
      • Try to do a sorting query such as :

        SELECT id FROM distanttable ORDER BY 1 LIMIT 1;

      If both servers are on the same physical network, both queries should take about the same time, in less than a second.

      On my test environement, the unsorted SELECT of the whole table takes about 0.3sec on both MariaDB and MySQL "clients" while the sorted query is taking about 8.5seconds on MariaDB while it does only takes about 0.3sec on MySQL.

      While the query is running, its state is "Sorting result".

      ps: replacing "SELECT id" by "SELECT 1" will make the query as fast as on MySQL.

      Attachments

        Activity

          People

            serg Sergei Golubchik
            jb-boin Jean Weisbuch
            Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.