June 19, 2013

Post: Long PRIMARY KEY for Innodb tables

October 3, 2006 By 8 Comments

…MyISAM tables in case of MySQL/System crash is painful and great to be avoided. Long primary key – why did not I use auto_increment id in…primary key itself does not get much larger whatever columns you place into it, as primary key BTREE contains all table data in…

Filed Under: Insight for DBAs Tagged With: ,

Post: MySQL Indexing Best Practices: Webinar Questions Followup

August 16, 2012 By 4 Comments

…in other direction. For Innodb Table id2 is not needed pas part of second key as PRIMARY key is appended to it internally anyway. For MyISAM table…MySQL optimizer restrictions in how well it can deal with primary key appended to the index column, especially in such case as you’re suggesting. In…

Filed Under: Insight for DBAs, MySQL

Post: InnoDB vs MyISAM vs Falcon benchmarks - part 1

January 8, 2007 By 117 Comments

…primary key and couple of extra indexes. CREATE TABLE IF NOT EXISTS `$tableName` ( `id` int(10) unsigned NOT NULL auto_increment, `name` varchar…KEY_POINT with only different the values of accessed column is stored in key. MyISAM…

Filed Under: Benchmarks Tagged With: ,

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 and… expected as MyISAM uses key compression for varchar columns so random key lookups become significantly slower. I tried to set pack_keys=0 which typically helps in similar…

Filed Under: Insight for Developers Tagged With: , , ,

Post: Dynamic row format for MEMORY tables

September 6, 2011 By 5 Comments

…in MySQL in 2008): The key columns must come before any non-key variable-length columns. For performance reasons, the key columns are stored in…TABLE sbtest (id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, c VARCHAR(4096) NOT NULL DEFAULT ”, PRIMARY KEY…

Filed Under: MySQL, Percona Software

Post: MySQL 6.0 vs 5.1 in TPC-H queries

March 25, 2008 By 17 Comments

…varchar(44) NOT NULL, `l_shipmode` char(10) NOT NULL, PRIMARY KEY (`l_orderkey`,`l_linenumber`), KEY `lineitem_fk2` (`l_suppkey`), KEY…as MySQL used index to scan rows and then did access to data to read other columns…

Filed Under: Benchmarks Tagged With:

Post: Enum Fields VS Varchar VS Int + Joined table: What is Faster?

January 24, 2008 By 30 Comments

…varchar(255) NOT NULL, PRIMARY KEY (id), KEY state (state) ) ENGINE=MyISAM; 2) Table with VARCHAR: CREATE TABLE cities_varchar ( id int(10) unsigned NOT NULL auto_increment, state varchar…

Filed Under: Benchmarks Tagged With: , , , , , , ,

Post: InnoDB Full-text Search in MySQL 5.6 (part 1)

February 26, 2013 By 1 Comment

…TABLE `dir_test_innodb` ( `id` int(10) unsigned NOT NULL, `full_name` varchar(100) DEFAULT NULL, `details` text, PRIMARY KEY (`id`), FULLTEXT KEY…mysql> CREATE TABLE dir_test_innodb4 (fts_doc_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY); ERROR 1166 (42000): Incorrect column…

Filed Under: Insight for DBAs, Insight for Developers, MySQL Tagged With: , , , , ,

Post: PBXT benchmarks

April 8, 2007 By 14 Comments

…primary key and couple of extra indexes. CREATE TABLE IF NOT EXISTS `$tableName` ( `id` int(10) unsigned NOT NULL auto_increment, `name` varchar…MySQL version We used MySQL 5.1.16-beta sources for MyISAM…KEY_POINT with only different the values of accessed column is stored in key… by Primary KEY, as I …

Filed Under: Benchmarks Tagged With:

Post: High-Performance Click Analysis with MySQL

December 22, 2008 By 13 Comments

…stored in a VARCHAR(36)?  When tables …MyISAM tables and taking downtime, I would not use MyISAM for anything but read-only tables…application in advance. What about partitioning in MySQL …primary key columns. And not only will every index be a little narrower, the table will now contain only half as…

Filed Under: Insight for Developers Tagged With: , , , ,
« Older Posts