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

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

  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