MariaDB Development
  1. MariaDB Development
  2. MDEV-4592

Correlated subquery produces inefficient optimizer plan for INFORMATION_SCHEMA

    Details

    • Type: Bug Bug
    • Status: Open
    • Priority: Minor Minor
    • Resolution: Unresolved
    • Affects Version/s: 10.0.2, 5.5.31
    • Fix Version/s: 10.0.11, 5.5.38
    • Labels:
    • Global Rank:
      3092

      Description

      (initial placeholder... ticket will be enhanced incrementally, I apologize for initial missing informations)

      apparently MariaDB 10.0.1, MariaDB 5.5.30, MySQL 5.5, 5.6 and Percona 5.5 are having the exact same behavior with a query that could be optimized, but I was expecting to run extremely quickly

      It has correlated subqueries to populate some of the outputted columns,
      but none of these are involved in the general WHERE clause

      plus, these is also a LIMIT 18...
      which brings down the whole thing to a very small dataset

      I was expecting the query optimizer to apply first the WHERE clause,
      than the LIMIT... then at last, execute the JOINS and the SUBQUERIES

      Instead (in my case I have a 5mio row table, and the database/versions I mentioned above, are all computing first all the joins and all the subqueries, bringing everything in memory, and then as last thing applying the WHERE and the LIMIT... which is very... ehhrrr... nonsense ...IMHO)

      I found a ticket, opened since 2007, on mysql.org, hitting exactly this scenario

      I will provide a suitable test to be filled into this ticket (as all my tests are now based on internal tools,
      and data dumps that can't be practically attached here), for now, I'm looking for comments...

        Issue Links

          Activity

          Hide
          Guido Serra added a comment -

          p.s. for the administrator, could u grant me EDIT permission on the ticket? so that I can enhance it with extra details

          Show
          Guido Serra added a comment - p.s. for the administrator, could u grant me EDIT permission on the ticket? so that I can enhance it with extra details
          Hide
          Sergei Golubchik added a comment -

          done. feel free to add more information

          Show
          Sergei Golubchik added a comment - done. feel free to add more information
          Hide
          Guido Serra added a comment -

          tnx @Sergei

          Show
          Guido Serra added a comment - tnx @Sergei
          Hide
          Guido Serra added a comment -

          uhmm... hold on...
          seems to be that my issue is specifically related to SQL_CALC_FOUND_ROWS
          forcing a full unnecessary construction of LEFT JOINS to get the count of results
          (without that, on a main table having 3mio rows, and the LEFT JOINS, and WHERE clause and the LIMIT...
          it takes 0.034 secs... against the 14.5 secs having the SQL_CALC_FOUND_ROWS)

          I can't isolate what I thought was the issue... therefore... closing

          I'll try to understand the code around the SQL_CALC_FOUND_ROWS stmt

          Show
          Guido Serra added a comment - uhmm... hold on... seems to be that my issue is specifically related to SQL_CALC_FOUND_ROWS forcing a full unnecessary construction of LEFT JOINS to get the count of results (without that, on a main table having 3mio rows, and the LEFT JOINS, and WHERE clause and the LIMIT... it takes 0.034 secs... against the 14.5 secs having the SQL_CALC_FOUND_ROWS) I can't isolate what I thought was the issue... therefore... closing I'll try to understand the code around the SQL_CALC_FOUND_ROWS stmt
          Hide
          Guido Serra added a comment - - edited

          just FYI... on a Macbook Air (same of the test above done on MariaDB10.0.1)
          with... MySQL 5.5.29

          and the same settings:

          • innodb_file_per_table
          • innodb_write_io_threads=16
          • innodb_read_io_threads=16
          • sort_buffer_size = 256M

          this specific bad query takes 159.7 secs

          Show
          Guido Serra added a comment - - edited just FYI... on a Macbook Air (same of the test above done on MariaDB10.0.1) with... MySQL 5.5.29 and the same settings: innodb_file_per_table innodb_write_io_threads=16 innodb_read_io_threads=16 sort_buffer_size = 256M this specific bad query takes 159.7 secs
          Hide
          Guido Serra added a comment -

          @Sergei: forgive me... feel free to close this ticket as a Won't Fix... it is NOT a MariaDB concern

          Show
          Guido Serra added a comment - @Sergei: forgive me... feel free to close this ticket as a Won't Fix... it is NOT a MariaDB concern
          Hide
          Guido Serra added a comment -

          for reference... http://bugs.mysql.com/bug.php?id=46648

          u probably have a similar situation... ur call if it is something bothering u... or not

          Show
          Guido Serra added a comment - for reference... http://bugs.mysql.com/bug.php?id=46648 u probably have a similar situation... ur call if it is something bothering u... or not

            People

            • Assignee:
              Unassigned
              Reporter:
              Guido Serra
            • Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

              • Created:
                Updated: