June 19, 2013

Post: INSERT ON DUPLICATE KEY UPDATE and summary counters.

May 29, 2006 By 48 Comments

INSERT … ON DUPLICATE KEY UPDATE is very powerful but often forgotten MySQL feature…. like this feature big deal because it is designed in truly MySQL style – very efficient solution for … ipstat(ip int unsigned not null primary key, hits int unsigned not null, last_hit timestamp); insert into …

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

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

February 26, 2013 By 1 Comment

… never been a very big fan of MyISAM; I would argue that in most situations… a TEXT “body”. CREATE TABLE `dir_test_innodb` ( `id` int(10) unsigned NOT NULL, `full_name` varchar(100) …done is this one, which discusses improving bulk insert performance. At the very bottom, the page claims that you can…

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

Post: High-Performance Click Analysis with MySQL

December 22, 2008 By 13 Comments

… very large amounts of space.  Choose primary keys very carefully…server for the initial insertion, because you can …int unsigned not null, clicks int unsigned not null, impressions int unsigned not null, blue_clicks int unsigned not null, blue_impressions int… all that fast on big joins; the query …

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

Post: Analyzing air traffic performance with InfoBright and MonetDB

October 2, 2009 By 50 Comments

…int(11) DEFAULT NULL, `DepTime` int(11) DEFAULT NULL, `DepDelay` int(11) DEFAULT NULL, `DepDelayMinutes` int(11) DEFAULT NULL, `DepDel15` int(11) DEFAULT NULL, `DepartureDelayGroups` int… ICE version is very limited and …so I generated big SQL file …InfoBright Enterprise allows INSERT / UPDATE but …

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

Post: Statistics of InnoDB tables and indexes available in xtrabackup

September 14, 2009 By 6 Comments

… ever wondered how big is that or another index in InnoDB … you had… CREATE TABLE `link_out104` ( `domain_id` int(10) unsigned NOT NULL, `link_id` int(10) unsigned NOT NULL auto_increment,…of cases we insert into the primary key in order which makes things very predictable, but the inserts into the …

Filed Under: Percona Software Tagged With: , ,

Post: Implications of Metadata Locking Changes in MySQL 5.5

February 1, 2013 By 9 Comments

…that is understandable), I have had lately some very interesting cases to deal with, with respect…metadata, a table ALTER that alters a big table can keep executing without any hindrance,…int(11) not null auto_increment primary key) ENGINE=InnoDB; Query OK, 0 rows affected (0.09 sec) session1 > insert…

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

Post: Recovering from a bad UPDATE statement

October 19, 2012 By 5 Comments

… `t1` ( `c1` int(11) NOT NULL AUTO_INCREMENT, `c2` varchar(10) NOT NULL, PRIMARY KEY (`c1`) ) ENGINE=InnoDB; INSERT INTO `t1` (`c2… ‘ MrSBUBMBAAAALAAAAAkDAAAAACcAAAAAAAEABHRlc3QAAnQxAAIDDwIKAAA= MrSBUBgBAAAAMgAAADsDAAAAACcAAAAAAAEAAv///AIAAAADdG9t/AIAAAAFdmFkaW0= ‘/*!*/; ### UPDATE test.t1 ### WHERE ### @1=2 /* INT meta=0 nullable=0 is_null=0 */ ### @2=’tom’ /* VARSTRING…, what if this UPDATE statement affected all rows in your very important big table? It might look more like this: UPDATE `t1…

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