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