I have this query which is flagged as a "slow query" by MariaDB and also causes Created_tmp_disk_tables
to increase each time it is run:
SELECT `articles`.`id`, `articles`.`title_id`, `articles`.`title`,
`articles`.`created_on`, `articles`.`abstract`, 'article' AS 'doctype'
FROM `articles` WHERE (published = 1) AND (highlight = 1)
UNION
SELECT `guides`.`id`, `guides`.`title_id`, `guides`.`title`,
`guides`.`created_on`, `guides`.`abstract`, 'guide' AS 'doctype'
FROM `guides` WHERE (published = 1) AND (highlight = 1)
ORDER BY `created_on` DESC LIMIT 2;
At first, I thought the root cause to be the abstract
field, since it was a TEXT
. Then I changed it to VARCHAR(1024)
but it didn't solve the issue (it-s still flagged as slow and still causes the tmp disk table).
Please note that if I remove the abstract
columns from the select-ed columns, the query is still flagged as slow, but it does not cause the tmp disk table.
Please could you help me understand what needs to be done to avoid temporary disk tables in this case?
The tables are as such:
Create table articles (
id Char(32) NOT NULL,
id_category Tinyint UNSIGNED NOT NULL,
title_id Varchar(255) NOT NULL,
title Varchar(255) NOT NULL,
author Varchar(100),
created_on Datetime NOT NULL,
modified_on Datetime,
published Bool NOT NULL DEFAULT true,
highlight Bool NOT NULL DEFAULT false,
abstract Varchar(1024),
content Mediumtext,
UNIQUE (title_id),
Primary Key (id)) ENGINE = InnoDB
DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
Create table guides (
id Char(32) NOT NULL,
id_category Tinyint UNSIGNED NOT NULL,
title_id Varchar(255) NOT NULL,
title Varchar(255) NOT NULL,
author Varchar(100),
created_on Datetime NOT NULL,
modified_on Datetime,
published Bool NOT NULL DEFAULT true,
highlight Bool NOT NULL DEFAULT false,
abstract Varchar(1024),
content Mediumtext,
UNIQUE (title_id),
Primary Key (id)) ENGINE = InnoDB
DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
Create Index visibility ON articles (published,highlight);
Create Index visibility ON guides (published,highlight);
This is the EXPLAIN:
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+--------------+------------+------+---------------+------------+---------+-------------+------+----------------+
| 1 | PRIMARY | articles | ref | visibility | visibility | 2 | const,const | 2 | |
| 2 | UNION | guides | ref | visibility | visibility | 2 | const,const | 4 | |
| NULL | UNION RESULT | <union1,2> | ALL | NULL | NULL | NULL | NULL | NULL | Using filesort |
And here is my MariaDB 10.1.26 configuration (I truncated entries supposed to be non-relevant):
innodb ON
innodb-adaptive-flushing TRUE
innodb-adaptive-flushing-lwm 10
innodb-adaptive-hash-index TRUE
innodb-adaptive-hash-index-partitions 1
innodb-adaptive-max-sleep-delay 150000
innodb-additional-mem-pool-size 8388608
innodb-api-bk-commit-interval 5
innodb-api-disable-rowlock FALSE
innodb-api-enable-binlog FALSE
innodb-api-enable-mdl FALSE
innodb-api-trx-level 0
innodb-autoextend-increment 64
innodb-autoinc-lock-mode 1
innodb-background-scrub-data-check-interval 3600
innodb-background-scrub-data-compressed FALSE
innodb-background-scrub-data-interval 604800
innodb-background-scrub-data-uncompressed FALSE
innodb-buf-dump-status-frequency 0
innodb-buffer-page ON
innodb-buffer-page-lru ON
innodb-buffer-pool-dump-at-shutdown FALSE
innodb-buffer-pool-dump-now FALSE
innodb-buffer-pool-dump-pct 100
innodb-buffer-pool-filename ib_buffer_pool
innodb-buffer-pool-instances 1
innodb-buffer-pool-load-abort FALSE
innodb-buffer-pool-load-at-startup FALSE
innodb-buffer-pool-load-now FALSE
innodb-buffer-pool-populate FALSE
innodb-buffer-pool-size 67108864
innodb-buffer-pool-stats ON
innodb-change-buffer-max-size 25
innodb-change-buffering all
innodb-changed-pages ON
innodb-checksum-algorithm INNODB
innodb-checksums TRUE
innodb-cleaner-lsn-age-factor HIGH_CHECKPOINT
innodb-cmp ON
innodb-cmp-per-index ON
innodb-cmp-per-index-enabled FALSE
innodb-cmp-per-index-reset ON
innodb-cmp-reset ON
innodb-cmpmem ON
innodb-cmpmem-reset ON
innodb-commit-concurrency 0
innodb-compression-algorithm zlib
innodb-compression-failure-threshold-pct 5
innodb-compression-level 6
innodb-compression-pad-pct-max 50
innodb-concurrency-tickets 5000
innodb-corrupt-table-action assert
innodb-data-file-path (No default value)
innodb-data-home-dir (No default value)
innodb-default-encryption-key-id 1
innodb-defragment FALSE
innodb-defragment-fill-factor 0.9
innodb-defragment-fill-factor-n-recs 20
innodb-defragment-frequency 40
innodb-defragment-n-pages 7
innodb-defragment-stats-accuracy 0
innodb-disable-sort-file-cache FALSE
innodb-doublewrite TRUE
innodb-empty-free-list-algorithm BACKOFF
innodb-encrypt-log FALSE
innodb-encrypt-tables OFF
innodb-encryption-rotate-key-age 1
innodb-encryption-rotation-iops 100
innodb-encryption-threads 0
innodb-fake-changes FALSE
innodb-fast-shutdown 1
innodb-fatal-semaphore-wait-threshold 600
innodb-file-format Antelope
innodb-file-format-check TRUE
innodb-file-format-max Antelope
innodb-file-io-threads 4
innodb-file-per-table TRUE
innodb-flush-log-at-timeout 1
innodb-flush-log-at-trx-commit 1
innodb-flush-method (No default value)
innodb-flush-neighbors 1
innodb-flushing-avg-loops 30
innodb-force-load-corrupted FALSE
innodb-force-primary-key FALSE
innodb-force-recovery 0
innodb-foreground-preflush EXPONENTIAL_BACKOFF
innodb-ft-aux-table (No default value)
innodb-ft-being-deleted ON
innodb-ft-cache-size 8000000
innodb-ft-config ON
innodb-ft-default-stopword ON
innodb-ft-deleted ON
innodb-ft-enable-diag-print FALSE
innodb-ft-enable-stopword TRUE
innodb-ft-index-cache ON
innodb-ft-index-table ON
innodb-ft-max-token-size 84
innodb-ft-min-token-size 3
innodb-ft-num-word-optimize 2000
innodb-ft-result-cache-limit 2000000000
innodb-ft-server-stopword-table (No default value)
innodb-ft-sort-pll-degree 2
innodb-ft-total-cache-size 640000000
innodb-ft-user-stopword-table (No default value)
innodb-idle-flush-pct 100
innodb-immediate-scrub-data-uncompressed FALSE
innodb-instrument-semaphores FALSE
innodb-io-capacity 200
innodb-io-capacity-max 18446744073709551615
innodb-kill-idle-transaction 0
innodb-large-prefix FALSE
innodb-lock-schedule-algorithm fcfs
innodb-lock-wait-timeout 50
innodb-lock-waits ON
innodb-locking-fake-changes TRUE
innodb-locks ON
innodb-locks-unsafe-for-binlog FALSE
innodb-log-arch-dir (No default value)
innodb-log-arch-expire-sec 0
innodb-log-archive FALSE
innodb-log-block-size 512
innodb-log-buffer-size 16777216
innodb-log-checksum-algorithm INNODB
innodb-log-compressed-pages TRUE
innodb-log-file-size 8388608
innodb-log-files-in-group 2
innodb-log-group-home-dir (No default value)
innodb-lru-scan-depth 1024
innodb-max-bitmap-file-size 104857600
innodb-max-changed-pages 1000000
innodb-max-dirty-pages-pct 75
innodb-max-dirty-pages-pct-lwm 0.001
innodb-max-purge-lag 0
innodb-max-purge-lag-delay 0
innodb-metrics ON
innodb-mirrored-log-groups 0
innodb-monitor-disable (No default value)
innodb-monitor-enable all
innodb-monitor-reset (No default value)
innodb-monitor-reset-all (No default value)
innodb-mtflush-threads 8
innodb-mutexes ON
innodb-old-blocks-pct 37
innodb-old-blocks-time 1000
innodb-online-alter-log-max-size 134217728
innodb-open-files 0
innodb-optimize-fulltext-only FALSE
innodb-page-size 16384
innodb-prefix-index-cluster-optimization FALSE
innodb-print-all-deadlocks FALSE
innodb-purge-batch-size 300
innodb-purge-threads 1
innodb-random-read-ahead FALSE
innodb-read-ahead-threshold 56
innodb-read-io-threads 4
innodb-read-only FALSE
innodb-replication-delay 0
innodb-rollback-on-timeout FALSE
innodb-rollback-segments 128
innodb-sched-priority-cleaner 19
innodb-scrub-log FALSE
innodb-scrub-log-speed 256
innodb-show-locks-held 10
innodb-show-verbose-locks 0
innodb-simulate-comp-failures 0
innodb-sort-buffer-size 1048576
innodb-spin-wait-delay 6
innodb-stats-auto-recalc TRUE
innodb-stats-include-delete-marked FALSE
innodb-stats-method nulls_equal
innodb-stats-modified-counter 0
innodb-stats-on-metadata FALSE
innodb-stats-persistent TRUE
innodb-stats-persistent-sample-pages 20
innodb-stats-sample-pages 8
innodb-stats-traditional TRUE
innodb-stats-transient-sample-pages 8
innodb-status-file FALSE
innodb-status-output FALSE
innodb-status-output-locks FALSE
innodb-strict-mode TRUE
innodb-support-xa TRUE
innodb-sync-array-size 1
innodb-sync-spin-loops 30
innodb-sys-columns ON
innodb-sys-datafiles ON
innodb-sys-fields ON
innodb-sys-foreign ON
innodb-sys-foreign-cols ON
innodb-sys-indexes ON
innodb-sys-semaphore-waits ON
innodb-sys-tables ON
innodb-sys-tablespaces ON
innodb-sys-tablestats ON
innodb-table-locks TRUE
innodb-tablespaces-encryption ON
innodb-tablespaces-scrubbing ON
innodb-thread-concurrency 0
innodb-thread-sleep-delay 10000
innodb-tmpdir (No default value)
innodb-track-changed-pages FALSE
innodb-trx ON
innodb-undo-directory .
innodb-undo-logs 128
innodb-undo-tablespaces 0
innodb-use-atomic-writes FALSE
innodb-use-fallocate FALSE
innodb-use-global-flush-log-at-trx-commit TRUE
innodb-use-mtflush FALSE
innodb-use-native-aio TRUE
innodb-use-stacktrace FALSE
innodb-use-sys-malloc TRUE
innodb-use-trim FALSE
innodb-write-io-threads 4
interactive-timeout 28800
join-buffer-size 262144
join-buffer-space-limit 2097152
join-cache-level 2
keep-files-on-create FALSE
key-buffer-size 131072
key-cache-age-threshold 300
key-cache-block-size 1024
key-cache-division-limit 100
key-cache-file-hash-size 512
key-cache-segments 0
large-files-support TRUE
large-pages FALSE
lc-messages en_US
lc-messages-dir /usr/share/mysql
lc-time-names en_US
local-infile TRUE
lock-wait-timeout 31536000
max-allowed-packet 16777216
max-binlog-cache-size 18446744073709547520
max-binlog-size 104857600
max-binlog-stmt-cache-size 18446744073709547520
max-connect-errors 100
max-connections 51
max-delayed-threads 20
max-digest-length 1024
max-error-count 64
max-heap-table-size 16777216
max-join-size 18446744073709551615
max-length-for-sort-data 1024
max-long-data-size 16777216
max-prepared-stmt-count 16382
max-relay-log-size 104857600
max-seeks-for-key 4294967295
max-session-mem-used 9223372036854775807
max-sort-length 1024
max-sp-recursion-depth 0
max-statement-time 0
max-tmp-tables 32
max-user-connections 0
max-write-lock-count 4294967295
memlock FALSE
metadata-locks-cache-size 1024
metadata-locks-hash-instances 8
min-examined-row-limit 0
mrr-buffer-size 262144
multi-range-count 256
open-files-limit 1024
optimizer-prune-level 1
optimizer-search-depth 62
optimizer-selectivity-sampling-limit 100
optimizer-switch index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on
optimizer-use-condition-selectivity 1
partition ON
performance-schema TRUE
pid-file /var/run/mysqld/mysqld.pid
plugin-dir /usr/lib/x86_64-linux-gnu/mariadb18/plugin/
plugin-maturity unknown
port 0
port-open-timeout 0
preload-buffer-size 32768
profiling-history-size 15
progress-report-time 5
protocol-version 10
query-alloc-block-size 16384
query-cache-limit 1048576
query-cache-min-res-unit 4096
query-cache-size 0
query-cache-strip-comments FALSE
query-cache-type OFF
query-cache-wlock-invalidate FALSE
query-prealloc-size 24576
range-alloc-block-size 4096
read-buffer-size 131072
read-only FALSE
read-rnd-buffer-size 262144
sort-buffer-size 2097152
sql-mode STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
stack-trace TRUE
stored-program-cache 256
strict-password-validation TRUE
super-large-pages FALSE
symbolic-links TRUE
sync-binlog 0
sync-frm TRUE
sync-master-info 10000
sync-relay-log 10000
sync-relay-log-info 10000
sysdate-is-now FALSE
system-time-zone CET
table-cache 400
table-definition-cache 400
table-open-cache 400
tc-heuristic-recover OFF
temp-pool TRUE
thread-cache-size 8
thread-concurrency 10
thread-handling one-thread-per-connection
thread-pool-idle-timeout 60
thread-pool-max-threads 1000
thread-pool-oversubscribe 3
thread-pool-size 4
thread-pool-stall-limit 500
thread-stack 196608
time-format %H:%i:%s
timed-mutexes FALSE
tmp-table-size 16777216
tmpdir /tmp
Best Answer
One factor that can cause the creation of temp disk tables is the inclusion of
TEXT
orBLOB
columns in theSELECT
s. To avoid this you can use theSUBSTR
function in theSELECT
to shorten the data as you may not need the full contents of that column in the result set. However, it seems aBLOB
/TEXT
query will only create a temporary disk table if other factors in the query cause the creation of temporary (non-disk) tables.Also,
UNION
is really shorthand forUNION DISTINCT
which means the database needs to eliminate duplicates to produce the result set. UseUNION ALL
instead of justUNION
to avoid what in this case looks like wasted effort. In many cases you will see that the UNION RESULT line disappears from the EXPLAIN because the database no longer needs to create a temporary table. According to the MariaDB Knowledge Base on UNION/UNION ALL:However, in your case, because of the
ORDER BY
at the end it will still create a temporary table, though it might not necessarily be a disk table. If you really want to avoid the temporary table, then you'll have to decide if you really need this ordering in the query. You will avoid the temporary table if you insteadORDER BY
on eachSELECT
. (Note that you then need to wrap eachSELECT
in parenthesis.)And just a thought, given that these two tables are identical except for their names, why not just merge them into one table and avoid the
UNION ... ORDER BY
-> temporary table problem?