2

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
  • 1
    As additional information: the system this DB is running on is a Debian 9.x with 1 core and 1 GB RAM. – Timido Jan 7 '18 at 11:54
  • Additional information request, please. Post on pastebin.com or here. A) complete (not edited) my.cnf-ini Text results of: B) SHOW GLOBAL STATUS; C) SHOW GLOBAL VARIABLES; D) complete MySQLTuner report if readily available - 7 or more days uptime is helpful E) SHOW ENGINE INNODB STATUS; Optional very helpful information, if available includes - htop, top & mytop for most active apps, ulimit -a for a linux/unix list of limits, iostat -x when system is busy for an idea of IOPS by device, for server tuning analysis. – Wilson Hauck Jun 17 '18 at 0:32
2

One factor that can cause the creation of temp disk tables is the inclusion of TEXT or BLOB columns in the SELECTs. To avoid this you can use the SUBSTR function in the SELECT to shorten the data as you may not need the full contents of that column in the result set. However, it seems a BLOB/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 for UNION DISTINCT which means the database needs to eliminate duplicates to produce the result set. Use UNION ALL instead of just UNION 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:

Until MariaDB 10.1.1, all UNION ALL statements required the server to create a temporary table. Since MariaDB 10.1.1, the server can in most cases execute UNION ALL without creating a temporary table, improving performance (see MDEV-334).

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 instead ORDER BY on each SELECT. (Note that you then need to wrap each SELECT 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?

2

1GB of RAM needs some serious issues taken...

CHAR(32) ... utf8 -- Is it really fixed length (CHAR)? Does it really need 3-byte utf8 characters? If this is some form of UUID or hash, consider using a smaller id. SMALLINT UNSIGNED is 2 bytes; what you have is potentially 96 bytes.

Do you need 2 different UNIQUE/PRIMARY keys? Perhaps title_id will suffice, thereby getting rid of id?

(@dbdemon's Answer nicely covers most of the issues directly relevant to the Question.)

Do you have anything else running on the same machine (or VM)? Keep an eye on swapping -- it is a performance killer. Meanwhile, combining the tables and adding a 3-column index should help in 2 or 3 ways to speed up the query (and lower I/O).

  • Yes, CHAR(32) is a UUID: it is generated as an MD5 hash. So a LATIN1 would be fine: is it safe to have different charset defined on different columns of the same table? – Timido Jan 7 '18 at 14:48
  • 1
    Oh and yes I have Apache mod_event + PHP-FPM running on the same machine. The traffic is not so high so at the moment I have no swap space used, fortunately. – Timido Jan 7 '18 at 14:50
  • 1
    @Timido - If you have not swap space and you do fill up the 1GB, then something (mysql/apache/php/etc) will be killed abruptly. – Rick James Jan 7 '18 at 15:00
  • @Timido - Even better is BINARY(16) (half the size) and use HEX() and UNHEX(). – Rick James Jan 7 '18 at 15:01
  • 2
    @Timido - OK, so my comments about shrinking the datatypes are not critical for your tiny machine. – Rick James Jan 7 '18 at 21:27
2

Since you know that none of the legs will contribute with more than two rows you may push LIMIT inside the legs of the union. Furthermore, since you know that the legs are disjoint (due to doctype) you can use UNION ALL instead of UNION (a smart optimizer could detect this on it's own). I beleive UNION is implemented via sort, so this may avoid additional sorts:

(SELECT articles.id, articles.title_id, articles.title,  
        articles.created_on, articles.abstract, 
        'article' AS doctype  
 FROM articles 
 WHERE published = 1 AND highlight = 1 
 ORDER BY created_on DESC LIMIT 2
)  
UNION ALL  
(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
) 
ORDER BY created_on DESC LIMIT 2;

In addition, you can change your indexes to:

Create Index visibility 
    ON articles (published,highlight,created_on desc);

Create Index visibility 
    ON guides (published,highlight,created_on desc);

Edit: A bit of the benefit of adding created_on to the indexes

Given the following query:

SELECT articles.id, articles.title_id, articles.title,  
        articles.created_on, articles.abstract, 
        'article' AS doctype  
 FROM articles 
 WHERE published = 1 AND highlight = 1 
 ORDER BY created_on DESC LIMIT 2

and the index:

(published,highlight)

the DBMS can use the index to find all rows where published = 1 AND highlight = 1. Since they may come in any order according to created_on they need to be sorted and finally, the last two created_on can be returned.

If we compare this with the index:

(published,highlight,created_on desc)

It is sufficient to read two index entries and return. Assuming there are a million rows that match the predicates, avoiding additional reads as well as sorting, will be beneficial for the performance of the query.

  • 1
    Until 8.0, DESC is ignored in CREATE INDEX. However, the absences of that feature has only a minor impact on performance. Having the index probably has a great impact. – Rick James Jan 7 '18 at 2:18
  • 1
    My DBMS is MariaDB 10.1.26. If DESC is ignored, can I still create the index based on the 3 columns ? that is: CREATE INDEX visibility ON articles (published, hightlight, created_on) Is this still beneficial compared to the index defined on the 2 columns only? – Timido Jan 7 '18 at 11:50
  • 1
    @Timido yes, it would be beneficial. – ypercubeᵀᴹ Jan 7 '18 at 13:27
  • Just wondering, does it add benefit even when I perform a query with a where-clause only on part of the index? say select * from articles where published = 1 ? – Timido Jan 28 '18 at 18:16
  • 1
    The 2-column index will probably be as good as the suggested index in this case (for most dbms). Some dbms (not mariadb AFAIK), can use skip-scan and benefit from the index even though a column is missing in the middle. – Lennart Jan 29 '18 at 21:17
1

Many thanks @dbdemon for the useful hints.

Your final thought about merging those two tables in a single one is definitely the way to go.

Since I'll have to modify the application code though, in the short term I did the following:

  1. added the ALL clause to UNION (ie: UNION ALL)
  2. further reduced the length of the abstract column to VARCHAR(400)

Btw, unfortunately I cannot remove the ORDER BY clause from the query: indeed I verified that it does cause the creation of a temporary table, but I'm going to live with it as long as it is not on DISK.

Reducing the length of VARCHAR down to 400 resulted in temporary disk tables not being created anymore ! (at a slight expense of memory usage) This is drastically evident from these metrics:

after reducing varchar to 400

To summarize my understanding: the ORDER BY in this case requires a temporary table. This table may be in memory, but due to the excessive length of the row ('abstract' column either TEXT or VARCHAR(1024)) MariaDb is not able to fit it in memory and uses the disk. Upon reducing the VARCHAR length to 400, MariaDb got able to fit the temporary table in memory! (I verified this happens as long as VARCHAR length is less than 512).

One additional thing I observed is that my schema uses UTF8 encoding. I guess that, similarly to MySql, MariaDb allocates the temporary table under a worst-case scenario. For VARCHAR this means allocating the longest possible size in bytes, which is the declared length multiplied by 3 (because of UTF8). See here: https://www.vividcortex.com/blog/2015/03/02/temp-tables-filesorts-utf8-varchar-schema-design-mysql/ , but I could not find any explicit reference to this in MariaDb documentation.

As a final notice, if I remove the ORDER BY clause, no temporary tables are created at all: not on disk, not in memory. This being the best solution but needs merging the two tables in a single one.

Btw: used this to check:

mysqladmin extended-status | grep -E "(Created_tmp_tables|Created_tmp_disk_tables)"
  • 1
    The graph disagrees with your setting of innodb_buffer_pool_size = 64M. (1G would be better for your 4GB machine.) – Rick James Jan 7 '18 at 2:24
  • 1
    @RickJames sorry for making this confusion: the setting is correct (64M). The graph shows the number of pages not the number of bytes. Since innodb_page_size = 16K , multiplying 16K by the maximum number of pages in the graph (4K) we get 64M. My machine has only 1 GB RAM. – Timido Jan 7 '18 at 11:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.