| Server IP : 185.86.78.101 / Your IP : 216.73.216.213 Web Server : Apache System : Linux 675867-vds-valikoshka1996.gmhost.pp.ua 5.4.0-150-generic #167-Ubuntu SMP Mon May 15 17:35:05 UTC 2023 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 : OFF Directory : /www/server/mysql/mysql-test/r/ |
Upload File : |
SELECT cost_name,cost_value FROM mysql.server_cost;
cost_name cost_value
disk_temptable_create_cost NULL
disk_temptable_row_cost NULL
key_compare_cost NULL
memory_temptable_create_cost NULL
memory_temptable_row_cost NULL
row_evaluate_cost NULL
SELECT engine_name,cost_name,cost_value FROM mysql.engine_cost;
engine_name cost_name cost_value
default io_block_read_cost NULL
default memory_block_read_cost NULL
CREATE TABLE t0 (
i1 INTEGER
);
INSERT INTO t0 VALUE (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
CREATE TABLE t1_innodb (
pk INTEGER PRIMARY KEY,
i1 INTEGER NOT NULL,
c1 CHAR(250),
c2 CHAR(250),
c3 CHAR(250),
c4 CHAR(250),
INDEX i1_key (i1)
) ENGINE=InnoDB;
INSERT INTO t1_innodb
SELECT a0.i1 + 10 * a1.i1, a0.i1, 'abc', 'def', 'ghi', 'jkl'
FROM t0 AS a0, t0 AS a1;
CREATE TABLE t1_myisam (
pk INTEGER PRIMARY KEY,
i1 INTEGER NOT NULL,
c1 CHAR(250),
c2 CHAR(250),
c3 CHAR(250),
c4 CHAR(250),
INDEX i1_key (i1)
) ENGINE=MyISAM;
INSERT INTO t1_myisam
SELECT a0.i1 + 10 * a1.i1, a0.i1, 'abc', 'def', 'ghi', 'jkl'
FROM t0 AS a0, t0 AS a1;
ANALYZE TABLE t1_innodb, t1_myisam;
Table Op Msg_type Msg_text
test.t1_innodb analyze status OK
test.t1_myisam analyze status Table is already up to date
"Explain with cost estimate against InnoDB"
EXPLAIN FORMAT=JSON SELECT * FROM t1_innodb;
EXPLAIN
{
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "29.00"
},
"table": {
"table_name": "t1_innodb",
"access_type": "ALL",
"rows_examined_per_scan": 100,
"rows_produced_per_join": 100,
"filtered": "100.00",
"cost_info": {
"read_cost": "9.00",
"eval_cost": "20.00",
"prefix_cost": "29.00",
"data_read_per_join": "99K"
},
"used_columns": [
"pk",
"i1",
"c1",
"c2",
"c3",
"c4"
]
}
}
}
Warnings:
Note 1003 /* select#1 */ select `test`.`t1_innodb`.`pk` AS `pk`,`test`.`t1_innodb`.`i1` AS `i1`,`test`.`t1_innodb`.`c1` AS `c1`,`test`.`t1_innodb`.`c2` AS `c2`,`test`.`t1_innodb`.`c3` AS `c3`,`test`.`t1_innodb`.`c4` AS `c4` from `test`.`t1_innodb`
"Explain with cost estimate against MyISAM"
EXPLAIN FORMAT=JSON SELECT * FROM t1_myisam;
EXPLAIN
{
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "46.63"
},
"table": {
"table_name": "t1_myisam",
"access_type": "ALL",
"rows_examined_per_scan": 100,
"rows_produced_per_join": 100,
"filtered": "100.00",
"cost_info": {
"read_cost": "26.63",
"eval_cost": "20.00",
"prefix_cost": "46.63",
"data_read_per_join": "99K"
},
"used_columns": [
"pk",
"i1",
"c1",
"c2",
"c3",
"c4"
]
}
}
}
Warnings:
Note 1003 /* select#1 */ select `test`.`t1_myisam`.`pk` AS `pk`,`test`.`t1_myisam`.`i1` AS `i1`,`test`.`t1_myisam`.`c1` AS `c1`,`test`.`t1_myisam`.`c2` AS `c2`,`test`.`t1_myisam`.`c3` AS `c3`,`test`.`t1_myisam`.`c4` AS `c4` from `test`.`t1_myisam`
UPDATE mysql.server_cost
SET cost_value=0.4
WHERE cost_name="row_evaluate_cost";
UPDATE mysql.engine_cost
SET cost_value=2.0
WHERE cost_name="memory_block_read_cost";
"Restarting MySQL server"
# restart
"MySQL restarted"
SELECT cost_name,cost_value FROM mysql.server_cost;
cost_name cost_value
disk_temptable_create_cost NULL
disk_temptable_row_cost NULL
key_compare_cost NULL
memory_temptable_create_cost NULL
memory_temptable_row_cost NULL
row_evaluate_cost 0.4
SELECT engine_name, cost_name,cost_value FROM mysql.engine_cost;
engine_name cost_name cost_value
default io_block_read_cost NULL
default memory_block_read_cost 2
"Explain with cost estimate against InnoDB"
EXPLAIN FORMAT=JSON SELECT * FROM t1_innodb;
EXPLAIN
{
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "58.00"
},
"table": {
"table_name": "t1_innodb",
"access_type": "ALL",
"rows_examined_per_scan": 100,
"rows_produced_per_join": 100,
"filtered": "100.00",
"cost_info": {
"read_cost": "18.00",
"eval_cost": "40.00",
"prefix_cost": "58.00",
"data_read_per_join": "99K"
},
"used_columns": [
"pk",
"i1",
"c1",
"c2",
"c3",
"c4"
]
}
}
}
Warnings:
Note 1003 /* select#1 */ select `test`.`t1_innodb`.`pk` AS `pk`,`test`.`t1_innodb`.`i1` AS `i1`,`test`.`t1_innodb`.`c1` AS `c1`,`test`.`t1_innodb`.`c2` AS `c2`,`test`.`t1_innodb`.`c3` AS `c3`,`test`.`t1_innodb`.`c4` AS `c4` from `test`.`t1_innodb`
"Explain with cost estimate against MyISAM"
EXPLAIN FORMAT=JSON SELECT * FROM t1_myisam;
EXPLAIN
{
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "93.27"
},
"table": {
"table_name": "t1_myisam",
"access_type": "ALL",
"rows_examined_per_scan": 100,
"rows_produced_per_join": 100,
"filtered": "100.00",
"cost_info": {
"read_cost": "53.27",
"eval_cost": "40.00",
"prefix_cost": "93.27",
"data_read_per_join": "99K"
},
"used_columns": [
"pk",
"i1",
"c1",
"c2",
"c3",
"c4"
]
}
}
}
Warnings:
Note 1003 /* select#1 */ select `test`.`t1_myisam`.`pk` AS `pk`,`test`.`t1_myisam`.`i1` AS `i1`,`test`.`t1_myisam`.`c1` AS `c1`,`test`.`t1_myisam`.`c2` AS `c2`,`test`.`t1_myisam`.`c3` AS `c3`,`test`.`t1_myisam`.`c4` AS `c4` from `test`.`t1_myisam`
UPDATE mysql.server_cost
SET cost_value=DEFAULT;
UPDATE mysql.engine_cost
SET cost_value=DEFAULT;
INSERT INTO mysql.engine_cost VALUES
("InnoDB", 0, "memory_block_read_cost", 4.0, CURRENT_TIMESTAMP, DEFAULT);
"Restarting MySQL server"
# restart
"MySQL restarted"
"Explain with cost estimate against InnoDB"
EXPLAIN FORMAT=JSON SELECT * FROM t1_innodb;
EXPLAIN
{
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "56.00"
},
"table": {
"table_name": "t1_innodb",
"access_type": "ALL",
"rows_examined_per_scan": 100,
"rows_produced_per_join": 100,
"filtered": "100.00",
"cost_info": {
"read_cost": "36.00",
"eval_cost": "20.00",
"prefix_cost": "56.00",
"data_read_per_join": "99K"
},
"used_columns": [
"pk",
"i1",
"c1",
"c2",
"c3",
"c4"
]
}
}
}
Warnings:
Note 1003 /* select#1 */ select `test`.`t1_innodb`.`pk` AS `pk`,`test`.`t1_innodb`.`i1` AS `i1`,`test`.`t1_innodb`.`c1` AS `c1`,`test`.`t1_innodb`.`c2` AS `c2`,`test`.`t1_innodb`.`c3` AS `c3`,`test`.`t1_innodb`.`c4` AS `c4` from `test`.`t1_innodb`
"Explain with cost estimate against MyISAM"
EXPLAIN FORMAT=JSON SELECT * FROM t1_myisam;
EXPLAIN
{
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "46.63"
},
"table": {
"table_name": "t1_myisam",
"access_type": "ALL",
"rows_examined_per_scan": 100,
"rows_produced_per_join": 100,
"filtered": "100.00",
"cost_info": {
"read_cost": "26.63",
"eval_cost": "20.00",
"prefix_cost": "46.63",
"data_read_per_join": "99K"
},
"used_columns": [
"pk",
"i1",
"c1",
"c2",
"c3",
"c4"
]
}
}
}
Warnings:
Note 1003 /* select#1 */ select `test`.`t1_myisam`.`pk` AS `pk`,`test`.`t1_myisam`.`i1` AS `i1`,`test`.`t1_myisam`.`c1` AS `c1`,`test`.`t1_myisam`.`c2` AS `c2`,`test`.`t1_myisam`.`c3` AS `c3`,`test`.`t1_myisam`.`c4` AS `c4` from `test`.`t1_myisam`
DELETE FROM mysql.engine_cost
WHERE engine_name NOT LIKE "default";
"Restarting MySQL server"
# restart
DROP TABLE t0, t1_innodb, t1_myisam;