…32) not null, fkto1 char(32) not null primary key, index(fkfrom1), index(fkfrom2), foreign key (fkto1) references dos1(fkfrom3) ) engine=innodb character … Oops, we forgot to disable foreign key checking. No problem, right? Not exactly…. (root@localhost) [oops4]> set foreign_key_checks=0; Query OK…
Post: To pack or not to pack - MyISAM Key compression
May 13, 2006 By 4 Comments
… PACK_KEYS=0|1|DEFAULT. Value 0 disables compression for all keys. Value 1 forces it for all keys. Value DEFAULT will use key compression…) NOT NULL default ”, KEY `c` (`c`), KEY `id` (`id`) ) ENGINE=MyISAM Index size: PACK_KEYS=DEFAULT – 1550K PACK_KEYS=1 – 1453K PACK_KEYS=0 – 8176K As…
Post: MySQL 5.6.10 Optimizer Limitations: Index Condition Pushdown
March 14, 2013 By 2 Comments
… type: ref possible_keys: role_id_note key: role_id_note key_len: 4 ref: const rows: 10259274 Extra: Using index condition 1 row…: ref possible_keys: role_id_note key: role_id_note key_len: 4 ref: const rows: 10259274 Extra: Using where; Using index 1 row…-like commands or optimizer_switch flags- we can disable ICP, but not “using index“. So, I wouldn’t call this a bug…
Post: Index lock and adaptive search - next two biggest InnoDB problems
February 25, 2010 By 12 Comments
… action is to disable adaptive_search (it is possible via global variable), but it rarely helps actually. With disabled adaptive index InnoDB needs to perform much more operations reading secondary keys. How it can be solved…
Post: Using CHAR keys for joins, how much is the overhead ?
June 18, 2007 By 18 Comments
… not on primary key and have query to read data for both tables. If the query would be index covering I would… may suffer significantly if key compression is not disabled Joining on Shorter CHAR keys is significantly faster than Long keys Latin1 (I guess any… bound workload results are likely to be different as longer indexes expected to have much worse cache fit, especially if you…
Post: Predicting how long data load would take
May 24, 2007 By 14 Comments
… data in primary key order into Innodb tables for optimal performance, but effect does not end here. If indexes are built by… bulk_insert_tree_size increase myisam_sort_buffer_size or key_buffer_size increase. Innodb typically needs large innodb_buffer_pool… well, such as disabling indexes before the load and enabling them back – helpful to make sure MyISAM build indexes via sorting not by…
Post: read_ahead (disabled) as steroid
May 12, 2009 By 4 Comments
… available here http://spreadsheets.google.com/ccc?key=rxUEhM2dqbX0uAfq9j6WQ_w ). Box Dell PowerEdge R900 (Does… and contains its nice features like FAST INDEX CREATION and dynamic pages. And XtraDB has… gaps can be smoother with disabled read_ahead, however disabling it does not show such improvement…
Post: Building Indexes by Sorting In Innodb (AKA Fast Index Creation)
June 19, 2012 By 2 Comments
… coded buffer for this operation, which means almost any such index build operation has to use excessive sort merge passes significantly… 1Mil rows Sysbench table. Buffer Length | alter table sbtest add key(c) 1MB 34 sec 8MB 26 sec 100MB 21 sec… REBUILD in this table is using “fast_index_creation=0″ which allows to disable fast index creation in Percona Server and force complete…
Post: How to load large files safely into InnoDB with LOAD DATA INFILE
July 3, 2008 By 29 Comments
… fast way to load data (especially if you disable unique key checks and foreign key checks), I recommended against this. There are several… bigint(20) NOT NULL, col2 bigint(20) default NULL, key(col1), key(col2) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 Here’s the result… logarithmic, which is what you should expect for B-Tree indexes. The two curves on the Cacti graph actually show both…
Post: Join Optimizations in MySQL 5.6 and MariaDB 5.5
April 4, 2012 By 4 Comments
… col1, then with Key-ordered scan, the secondary key on col1 would be sorted by col1 i.e. in index order and then… 5.5 w/ buffer size 6M (Hash Join and Key-ordered Scan disabled) You can see that the lowest query time is… MariaDB with Hash Join and Key-ordered Scan enabled, and MariaDB with Hash Join and Key-ordered Scan disabled, and the only difference…
