… primary key order: mysql> explain select * from innodb order by id \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: pktest type: index possible_keys: NULL key: PRIMARY key_len: 4 ref: NULL rows: 6 Extra: 1 row in set (0.00 sec) mysql> explain select * from myisam order by…
Post: Long PRIMARY KEY for Innodb tables
October 3, 2006 By 8 Comments
… key will refer to the rows by primary key. I also recommended to use sequential primary keys so you do not end up having random primary key… – checking/repairing large MyISAM tables in case of MySQL/System crash is painful and great to be avoided. Long primary key – why did not… rather than before it in the index ? They are not selective plus we might want to get all thumbnails for given…
Post: InnoDB vs MyISAM vs Falcon benchmarks - part 1
January 8, 2007 By 117 Comments
… MySQL version We used MySQL 5.1.14-beta sources for MyISAM / InnoDB and MySQL 5.1.14-falcon bitkeeper tree bk://mysql.bkbits.net/mysql… with access by primary key. InnoDB is faster than MyISAM by 6-9%. Falcon shows very bad scalabilty. READ_KEY_POINT Query: SELECT name FROM…
Post: MyISAM Scalability and Innodb, Falcon Benchmarks
October 12, 2007 By 10 Comments
…key_len: 4 ref: const rows: 4092 Extra: *************************** 2. row *************************** id: 1 select_type: SIMPLE table: t1 type: eq_ref possible_keys: PRIMARY,id key: PRIMARY key… NOT NULL, PRIMARY KEY (`id`), KEY `val` (`val`) ) ENGINE=MyISAM… results for MyISAM using MySQL 5….
Post: MySQL VIEW as performance troublemaker
August 12, 2007 By 53 Comments
…) unsigned NOT NULL, `message` text NOT NULL, PRIMARY KEY (`user_id`,`comment_id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 So how would you get…: index possible_keys: NULL key: PRIMARY key_len: 8 ref: NULL rows: 1792695 Extra: Using index 2 rows in set (0.96 sec) mysql> select * from…
Post: High-Performance Click Analysis with MySQL
December 22, 2008 By 13 Comments
…repairing huge MyISAM tables and taking downtime, I would not use MyISAM for …indexes other than the primary key, we can shrink the primary key‘s width: create table… it on the master with INSERT..SELECT queries, it will propagate to …either using MySQL 5.1′s row-based replication, or in MySQL 5.0…
Post: Join performance of MyISAM and Innodb
May 29, 2006 By 6 Comments
…, `pad` char(8) default NULL, PRIMARY KEY (`id`), KEY `i` (`i`), KEY `c` (`c`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8; select count(t1.pad),count(t2.pad… lookups MyISAM generally has lower processing overhead due to its simplicity MyISAM still a bit better by primary key join than for secondary key join… which matches the index, so there is no need for MySQL to request next row matching index Note: This applies to…
Post: Using CHAR keys for joins, how much is the overhead ?
June 18, 2007 By 18 Comments
… for MySQL 5.1.18 using MyISAM and Innodb tables. This time unlike other benchmarks I decided to do Join not on primary key…, `j` int(10) unsigned NOT NULL, KEY `i` (`i`), KEY `j` (`j`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 mysql> select sum(t1.i+t2.j+length… if someone curious, it did not really change beside key lengths: mysql> explain select sum(t1.i+t2.j+length(t2.c)+t1…
Post: To pack or not to pack - MyISAM Key compression
May 13, 2006 By 3 Comments
…primary key is handled. I can’t see the index size difference between DEFAULT and 1 values for such key…KEY `c` (`c`), KEY `id` (`id`) ) ENGINE=MyISAM Index size: PACK_KEYS=DEFAULT – 1550K PACK_KEYS=1 – 1453K PACK_KEYS…select…
Post: To UUID or not to UUID ?
March 13, 2007 By 28 Comments
… in MySQL 5.1 Data Clustering This again applies to Innodb tables aspect of primary key selection – you often can gain a lot by selecting primary key…’ve created MyISAM tables containing just integer auto_increment primary key and containing char(36) value and used for UUID primary key and when I…
