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 
