Server IP : 104.21.38.3 / Your IP : 162.158.88.144 Web Server : Apache System : Linux krdc-ubuntu-s-2vcpu-4gb-amd-blr1-01.localdomain 5.15.0-142-generic #152-Ubuntu SMP Mon May 19 10:54:31 UTC 2025 x86_64 User : www ( 1000) PHP Version : 7.4.33 Disable Function : passthru,exec,system,putenv,chroot,chgrp,chown,shell_exec,popen,proc_open,pcntl_exec,ini_alter,ini_restore,dl,openlog,syslog,readlink,symlink,popepassthru,pcntl_alarm,pcntl_fork,pcntl_waitpid,pcntl_wait,pcntl_wifexited,pcntl_wifstopped,pcntl_wifsignaled,pcntl_wifcontinued,pcntl_wexitstatus,pcntl_wtermsig,pcntl_wstopsig,pcntl_signal,pcntl_signal_dispatch,pcntl_get_last_error,pcntl_strerror,pcntl_sigprocmask,pcntl_sigwaitinfo,pcntl_sigtimedwait,pcntl_exec,pcntl_getpriority,pcntl_setpriority,imap_open,apache_setenv MySQL : OFF | cURL : ON | WGET : ON | Perl : ON | Python : OFF | Sudo : ON | Pkexec : ON Directory : /www/server/mysql/src/mysql-test/suite/opt_trace/t/ |
Upload File : |
--source include/have_optimizer_trace.inc --source include/have_64bit.inc if (`SELECT $PS_PROTOCOL + $SP_PROTOCOL + $CURSOR_PROTOCOL + $VIEW_PROTOCOL > 0`) { --skip Need normal protocol } SET optimizer_trace_max_mem_size=1048576; # 1MB SET optimizer_trace="enabled=on,one_line=off"; SET end_markers_in_json="on"; CREATE TABLE tmp ( pk INT PRIMARY KEY AUTO_INCREMENT, col1 CHAR (1) ); INSERT INTO tmp(col1) VALUES ('a'),('b'),('c'),('d'),('e'),('f'),('g'),('h'); CREATE TABLE t1 ( uniq VARCHAR(10), col1 VARCHAR(10), col2 VARCHAR(1024) ); INSERT INTO t1 SELECT pk, col1, col1 FROM tmp; --source include/turn_off_only_full_group_by.inc # Uses MyISAM temporary table due to long VARCHAR in GROUP BY clause. SELECT uniq, col1 FROM t1 GROUP BY col2,uniq LIMIT 3; --replace_result InnoDB TMP_TABLE_ENGINE MyISAM TMP_TABLE_ENGINE SELECT * FROM information_schema.OPTIMIZER_TRACE; # Uses HEAP temporary table SELECT uniq, col1, col2 FROM t1 GROUP BY uniq ; --replace_result InnoDB TMP_TABLE_ENGINE MyISAM TMP_TABLE_ENGINE SELECT * FROM information_schema.OPTIMIZER_TRACE; select @@tmp_table_size; SET @old_size= @@tmp_table_size; SET SESSION tmp_table_size= 1024; INSERT INTO t1 SELECT pk+8, col1, col1 FROM tmp; # Uses HEAP temporary table. Converts it to MyISAM due to heap size limitation SELECT uniq, col1, col2 FROM t1 GROUP BY uniq; --replace_result InnoDB TMP_TABLE_ENGINE MyISAM TMP_TABLE_ENGINE SELECT * FROM information_schema.OPTIMIZER_TRACE; SET GLOBAL tmp_table_size= @old_size; # Temp tables for I_S tables. Uses HEAP temporary table. # Converts it to MyISAM packed record format due to heap size limitation SELECT pool_id FROM information_schema.innodb_buffer_page LIMIT 1; --replace_result InnoDB TMP_TABLE_ENGINE MyISAM TMP_TABLE_ENGINE SELECT * FROM information_schema.OPTIMIZER_TRACE; --source include/restore_sql_mode_after_turn_off_only_full_group_by.inc DROP TABLE t1, tmp; --echo # --echo # Bug#17231940: THE OPTIMIZER STILL USES FIXED LENGTH TEMPORARY TABLES --echo # ON DISK --echo # CREATE TABLE t1 ( c1 INT AUTO_INCREMENT PRIMARY KEY, c2 VARCHAR(250) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; INSERT INTO t1(c2) VALUES ('b'),('b'); INSERT INTO t1(c2) SELECT t1.c2 FROM t1, t1 t2, t1 t3, t1 t4, t1 t5, t1 t6; SET @@max_heap_table_size=1; SET @@group_concat_max_len= 500; # This query creates 4 tmp tables, 3 of them aren''t packed without this # fix. # Purpose Format without fix / with fix # union result fixed packed # derived tbl result fixed packed # join materialization fixed packed # group_concat internal tbl packed packed (not shown in the opt trace) SELECT c1,GROUP_CONCAT(c2) cc FROM (SELECT * FROM t1 UNION SELECT c1, 'a' FROM t1) tt GROUP BY c1 ORDER BY cc; --replace_result InnoDB TMP_TABLE_ENGINE MyISAM TMP_TABLE_ENGINE SELECT * FROM information_schema.OPTIMIZER_TRACE; DROP TABLE t1; --echo #