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

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





      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...

        (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




            serg Sergei Golubchik
            jan.reges Ján Regeš
            1 Vote for this issue
            5 Start watching this issue



              Git Integration

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