…int(10) unsigned NOT NULL DEFAULT ’0′, `mid` int(10) unsigned NOT NULL DEFAULT ’0′, `id` bigint(20) unsigned NOT NULL, `k` int(10) unsigned…VALUES(k)+k; COMMIT; That is, our PRIMARY KEY is not sequential anymore, which is bad for… = 50 max_connections = 2000 max_prepared_stmt_count=500000 max_connect_…
Comment: Speeding up GROUP BY if you want aproximate results
April 9, 2008 By 24 Comments
Andrew, Something wrong with your query result – note 2147483647 value for CRC32 for few columns – this is MAX value for SIGNED int while CRC32 should be unsigned. Also for BINARY CRC32 you can get above this value. You might have found a bug in MySQL 
Post: PROCEDURE ANALYSE
March 23, 2009 By 9 Comments
…to find the optimal data type for the column value – for example column is defined as INT but is it really needed…(3) UNSIGNED NOT NULL *************************** 3. row *************************** Field_name: percona_drupal.node.type Min_value: blog Max_value: webform Min_length: 4 Max_length:…
Post: Edge-case behavior of INSERT...ODKU
October 27, 2012 By 6 Comments
…’ve used in my demonstration table – INT UNSIGNED. Do the math. The maximum value for an auto-increment INT UNSIGNED is 4294967295. Divide that by 1500…-increment max value, which is the one that got updated. The secondary UNIQUE on username is, for all intents and purposes, ignored. For the…
Post: Can you Trust CHECK TABLE ?
September 11, 2008 By 6 Comments
… particular case I replaced .frm file for the table from different one changing INT to UNSIGNED INT to see what effect it will give… by CHECK TABLE, which does store values larger than max signed int but which sorts them as unsigned ints. Quite fun. I hope the task…
Post: InnoDB vs MyISAM vs Falcon benchmarks - part 1
January 8, 2007 By 117 Comments
… TABLE IF NOT EXISTS `$tableName` ( `id` int(10) unsigned NOT NULL auto_increment, `name` varchar(64)…-file-size=100M –innodb-thread-concurrency=8 –max-connections=1500 –table-cache=512 –net_read_… but very quick query to retrieve value from PK. The results for InnoDB and MyISAM are comparable…
Post: How many partitions can you have ?
December 5, 2009 By 19 Comments
…int(10) unsigned NOT NULL, `c` int(10) unsigned NOT NULL, PRIMARY KEY (`id`), KEY(c) ) ENGINE=InnoDB PARTITION BY RANGE(id) ( PARTITION p100000 VALUES…for 1 partitions, 10 seconds for 10 partitions, 16 seconds for 100 partitions and 23 seconds for… as in reality only 1 (max 2) partitions got data …
Post: How much space does empty Innodb table take ?
December 16, 2008 By 7 Comments
… everything: CREATE TABLE `test_innodb` ( `i` int(10) unsigned NOT NULL, `c` char(100) default NULL, PRIMARY…value is going to be changing back and forth and it would be very inaccurate for small tables. For… 1069 Avg_row_length: 199 Data_length: 212992 Max_data_length: 0 Index_length: 360448 Data_…
Post: Recovering Innodb table Corruption
July 4, 2008 By 22 Comments
…TABLE in INNODB is pretty useless. For my manually corrupted table I am … ( -> `c` char(255) DEFAULT NULL, -> `id` int(10) unsigned NOT NULL AUTO_INCREMENT, -> PRIMARY KEY (`id`) -> …max PK which could be recovered and try some higher values mysql> select max(id) from test2; +———+ | max(id) | …
Post: How much overhead is caused by on disk temporary tables
August 16, 2007 By 26 Comments
… Pentium 4 box – something I had available for testing. CREATE TABLE `gt` ( `i` int(10) unsigned NOT NULL, `c` char(50) NOT NULL… to 128M ? mysql> show global status like “key%”; +————————+———+ | Variable_name | Value | +————————+———+ | Key_blocks_not_flushed | 49361 | | Key_blocks_unused | 61201 | | Key… value but it does not affect current session. A little gotcha which still catches me. For 1M rows we have mysql> set max…
