June 19, 2013

Post: Adventures in archiving

February 11, 2013 By 16 Comments

… NULL AUTO_INCREMENT, `dat` varchar(1000) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4; CREATE…) NOT NULL AUTO_INCREMENT, `dat` varchar(1000) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; As can be seen we have the… ahead and using it. Also you need to be aware that Archive does not have good performance. e.g. mysql> select id…

Filed Under: MySQL Tagged With: , ,

Post: Connecting orphaned .ibd files

May 13, 2011 By 19 Comments

…PRIMARY KEY (`TABLE_ID`,`ID`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 Please note field SPACE. For table actor it is equal to 15: mysql…. As …VARCHAR(45) NOT NULL, last_update TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (actor_id), KEY… used. It will refuse …

Filed Under: MySQL

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

February 26, 2013 By 1 Comment

… NOT NULL, `full_name` varchar(100) DEFAULT NULL, `details` text, PRIMARY KEY (`id`), FULLTEXT KEY `full_name` (`full_name…mysql> CREATE TABLE dir_test_innodb4 (fts_doc_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY);… customers, and as an end user, I might have a hard time using the feature…

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

Post: High-Performance Click Analysis with MySQL

December 22, 2008 By 13 Comments

… in a VARCHAR(36)?  When tables get big, every byte matters a lot.  Use the smallest …’t use GUIDs.  Which brings me to my next point: Use InnoDB Assuming that you will use the stock MySQL …, you should define the primary key as (day, ad).  Don’t use an auto-increment primary key, and don’t put …

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

Post: Dynamic row format for MEMORY tables

September 6, 2011 By 5 Comments

…the MEMORY storage engine (also known as HEAP) in MySQL has been one of the long…using this new feature (and these points are probably the reason why it was not included in MySQL in 2008): The key… NOT NULL AUTO_INCREMENT, c VARCHAR(4096) NOT NULL DEFAULT ”, PRIMARY KEY (id)) ENGINE=MEMORY ROW_FORMAT…

Filed Under: MySQL, Percona Software

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

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

Post: Handling big result sets

June 26, 2006 By 13 Comments

…, `f32` varchar(39) default NULL, `f33` date default NULL, `f34` smallint(6) default NULL, `f35` int(11) NOT NULL, PRIMARY KEY (`f1`) ); select count(*) from longf; +———-+ | count(*) | +———-+ | 5242880 | +———-+ And simple PHP script which retrieves all rows from the table: < ?php $link = mysql… 2.53 times. It is often though mysql_store_result is faster than mysql_use_result, as you can see in certain conditions it…

Filed Under: Insight for Developers Tagged With: ,

Post: Percona XtraDB Cluster: Multi-node writing and Unexpected deadlocks

August 17, 2012 By 17 Comments

… locking is what Innodb does as a transaction executes.  All the …but Galera does not use pessimistic locking cluster-wide, it uses optimistic locking. …varchar(32) DEFAULT NULL, PRIMARY KEY (`i`) node2 mysql> insert into autoinc (j) values (‘node2′ ); Query OK, 1 row affected (0.00 sec) node2 mysql…

Filed Under: MySQL, XtraDB Cluster

Post: The Optimization That (Often) Isn't: Index Merge Intersection

December 14, 2012 By 6 Comments

… DEFAULT 0, username VARCHAR(20), … other columns here … PRIMARY KEY(user_id), INDEX `parent_…Using intersect(user_type,status,parent_id); Using where; Using index; Using filesort At first glance, this might not look too bad. MySQL is using…. We could rewrite the query as a sub-select. This will …

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

Post: Edge-case behavior of INSERT...ODKU

October 27, 2012 By 6 Comments

…VARCHAR(20) NOT NULL, host_id TINYINT UNSIGNED NOT NULL, last_modified TIMESTAMP NULL DEFAULT NULL, PRIMARY KEY(id), UNIQUE KEY…and the PRIMARY KEY is always going to be considered as being defined first. So, MySQL checks our…on their table is the same as what I’ve used in my demonstration table – INT…

Filed Under: Insight for DBAs, Insight for Developers, MySQL
« Older Posts
Newer Posts »