… NULL, pad CHAR(60) DEFAULT ” NOT NULL, PRIMARY KEY (id) ) CREATE INDEX k on sbtest$I(k) and tables sbtest$I_r10… thread_stack = 192K tmp_table_size = 64M server-id = 10 #*** MyISAM Specific options key_buffer_size = 8M read_buffer_size = 1M… = 8M myisam_sort_buffer_size = 8M myisam_max_sort_file_size = 10G #myisam_max_extra_sort_file_size = 10G myisam_repair_threads = 1 myisam_recover…
Post: Air traffic queries in MyISAM and Tokutek (TokuDB)
November 5, 2009 By 26 Comments
… account, that for MyISAM we need to choose proper indexes to execute queries effectively, and there is pain coming with indexes: – load of… for both MyISAM and TokuDB in empty table with created indexes. Load time for MyISAM: 16608 sec For TokuDB: 19131 sec Datasize (including indexes) MyISAM: 36…
Post: Should you name indexes while doing ALTER TABLE ?
May 28, 2008 By 10 Comments
… NULL, KEY `i` (`i`,`j`), KEY `i_2` (`i`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 1 row in set (0.00 sec… of maintaining the indexes. What happens if you try to create the same index without specifying index name ? MySQL will happily create (and maintain) as… sec) However if you would specify index name MySQL will complain if you try to create index with same name again: mysql> alter…
Post: Ultimate MySQL variable and status reference list
January 8, 2011 By 4 Comments
…myisam_recoverblogpercona.commanual myisam_recover_optionsblogpercona.commanual myisam_recover_optionsblogpercona.commanual myisam_repair_threadsblogpercona.commanual myisam_sort_buffer_sizeblogpercona.commanual myisam_stats_methodblogpercona.commanual myisam…
Post: ANALYZE: MyISAM vs Innodb
September 3, 2008 By 9 Comments
… how accurate stats we can get for for Index Stats created by ANALYZE TABLE for MyISAM and Innodb. But before we go into…MYISAM: mysql> show index from antest; +——–+————+———-+————–+————-+———–+————-+———-+——–+——+————+———+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index…
Post: To pack or not to pack - MyISAM Key compression
May 13, 2006 By 4 Comments
… the following will apply: Forward range/index scan will be fast (read_next) as MyISAM will remember previous position and will not… large. Let us try couple of benchmarks now. I’ve created simple table with 100.000 rows with columns. And I…) NOT NULL default ”, KEY `c` (`c`), KEY `id` (`id`) ) ENGINE=MyISAM Index size: PACK_KEYS=DEFAULT – 1550K PACK_KEYS=1 – 1453K PACK…
Post: MyISAM Scalability and Innodb, Falcon Benchmarks
October 12, 2007 By 10 Comments
… NOT NULL, PRIMARY KEY (`id`), KEY `id` (`id`,`stat`) ) ENGINE=MyISAM; CREATE TABLE `t2` ( `id` int(11) unsigned NOT NULL auto_increment… while multiple indexes are used you can create multiple key caches and map those to them. In this case however single index had…. Here is comparison of MyISAM Innodb and Falcon results in the graphical form: I’ve created bug, for MyISAM key cache contention issue…
Post: Recovering CREATE TABLE statement from .frm file
December 17, 2008 By 5 Comments
…the table contains indexes that …Create Table | +————-+—————————————————————————————————————————-+ | test_myisam | CREATE TABLE `test_myisam` ( `a` int(11) DEFAULT NULL, `b` int(11) DEFAULT NULL ) ENGINE=MyISAM…
Post: MySQL Optimizer and Innodb Primary Key
October 3, 2006 By 9 Comments
… KEY `a` (`a`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; “myisam” is same table created with MyISAM storage engine used to show difference: MySQL Optimizer…value can be read from index, making some queries index covered which previously was not: Notice “Using Index” difference mysql> explain select id…
Post: Concatenating MyISAM files
November 19, 2012 By 5 Comments
… null, primary key (id)) engine=myisam; Query OK, 0 rows affected (0.01 sec) mysql> create table test_concat_part like test… record delete-chain – recovering (with sort) MyISAM-table ‘test_concat’ Data records: 3 – Fixing index 1 myisamchk: error: Couldn’t fix table… -r test_concat – recovering (with sort) MyISAM-table ‘test_concat’ Data records: 6 – Fixing index 1 mysql> select * from test_concat; +—-+——-+ | id…
