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

SELECT is 2-3x slower when used as source for other construct

    XMLWordPrintable

    Details

      Description

      Hi.

      I'm using TokuDB tables with COMPRESSION=tokudb_small. MariaDB 5.5.33a with TokuDB support. Builded from amd64 sources with cmake.

      I have one SELECT, which returns about 100 records with 20 number columns. These records are aggregated from 1 mio records in 3 joined tables, by indexed columns. It's relatively simple, with predictable and clean execution plan.

      This select is running stable about 4,8 seconds. I use SQL_NO_CACHE flag.

      But, when this same SELECT is part of any other simple construct (for example CREATE TABLE [select], or INSERT INTO [select] ON DUPLICATE KEY UPDATE), this query is running 2-3x slower, about 13 seconds.

      In query profile, or status, there are identical informations (Handler_read_key, etc.) for both usages.

      But with one important difference - durations of query steps:

      • in profile of first usage (just SELECT):
      • "Queried about 140000 rows" with duration 0.0518770
      • "Queried about 150000 rows" with duration 0.0504550
      • etc..
      • in profile of second usage (SELECT for CREATE TABLE, or INSERT/UPDATE), profile contains:
      • "Queried about 140000 rows" with duration 0.1185300
      • "Queried about 150000 rows" with duration 0.1221410

      So, when SELECT is part of other construct, this select is about 2-3x slower.

      Just for sure, i used also index-hinting for every joined table. These hints are the same, which EXPLAIN reports. So, execution plan is same in both usages.

      I used also "SQL_NO_CACHE" flag, for securing identical environment. SELECT returns just 100 records, so, there is no big related overhead with creating new temporary table, or inserting/updating rows. When this SELECT is vestured in other SELECT * FROM [select] LIMIT 1, result is the same.

      Do you know about this performance issue?

      Thank you.

      SELECT looks like...

       
      SELECT
        x.col1,
        x.col2,
        (x.col1 - x.col2) AS col3
      FROM (
        SELECT cols [with some SUM, COUNT, AVG aggreations]
        FROM table1
        INNER JOIN table2 ON table2.id = table1.id_x
        INNER JOIN table3 ON table3.id = table2.id_y
        WHERE id_date = 5
        GROUP BY table1.id_z
      ) AS x

        Attachments

          Activity

            People

            Assignee:
            serg Sergei Golubchik
            Reporter:
            jan.reges Ján Regeš
            Votes:
            1 Vote for this issue
            Watchers:
            5 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved: