…was originally built is long over, but still…int(11) NOT NULL, `productid` int(11) NOT NULL, `price` float NOT NULL, PRIMARY KEY (`transactionid`), KEY `marketsegment` (`price`,`customerid`), KEY `registersegment` (`cashregisterid`,`price`,`customerid`), KEY…no. of secondary indexes or even going away…
Post: Long PRIMARY KEY for Innodb tables
October 3, 2006 By 8 Comments
…smallint(5) unsigned NOT NULL default ’0′, `size` int(10) unsigned NOT NULL default ’0′, `pathversion…it will all come from one or few close pages, saving IO dramatically. …long primary keys but usually it is not major. Non Sequential primary key This just comes as an effect of choosing url as the key…
Post: Avoiding auto-increment holes on InnoDB with INSERT IGNORE
November 29, 2011 By 7 Comments
…. The problems are even worse with long running queries like INSERT INTO table1 … SELECT ……simple insert query like a single-row or multi-row INSERT/REPLACE uses a …foo` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` int(11) DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `uniqname` (`name`) )…
Post: To SQL_CALC_FOUND_ROWS or not to SQL_CALC_FOUND_ROWS?
August 28, 2007 By 53 Comments
…post I’ll try to check, is this true or not and when it is better to run …int(10) NOT NULL auto_increment, `b` int(10) NOT NULL, `c` int(10) NOT NULL, `d` varchar(32) NOT NULL, PRIMARY KEY (`a`), KEY…consecutive runs. And now – we need too check how long our COUNT query would take: mysql> SELECT SQL_…
Post: Using CHAR keys for joins, how much is the overhead ?
June 18, 2007 By 18 Comments
… do Join not on primary key and have query to … good meaning for query or schema. For joins which…int(10) unsigned NOT NULL, `c` char(10) DEFAULT NULL, `j` int(10) unsigned NOT NULL, KEY `i` (`i`), KEY…key compression is not disabled Joining on Shorter CHAR keys is significantly faster than Long keys…
Post: High-Performance Click Analysis with MySQL
December 22, 2008 By 13 Comments
…, ad int unsigned not null, is_blue tinyint unsigned not null, clicks int unsigned not null, impressions int unsigned not null, …. primary key(day, ad…, clicks int unsigned not null, impressions int unsigned not null, blue_clicks int unsigned not null, blue_impressions int unsigned not null, …. primary key(day, ad… avoid sharding for a long time and run on many fewer disk drives with much less memory, or even with 10-15x…
Post: A workaround for the performance problems of TEMPTABLE views
May 19, 2010 By 7 Comments
…filter been merged into the view query. As long as a view avoids aggregation, DISTINCT, GROUP BY… TABLE `t2` ( `c1` int(11) DEFAULT NULL, `c2` int(11) NOT NULL AUTO_INCREMENT, PRIMARY KEY (`c2`), KEY `c1` (`c1`) ) …parameter table. This table might be view specific or you could construct it in such a …
Post: Connecting orphaned .ibd files
May 13, 2011 By 12 Comments
…single tablespace. This is typicaly one or several ibdata files. A well known…int(10) unsigned default NULL, `TYPE` int(10) unsigned default NULL, `SPACE` int(10) unsigned default NULL, `PAGE_NO` int(10) unsigned default NULL, PRIMARY KEY…InnoDB pages. A page is 16k long (UNIV_PAGE_SIZE in the source …
Post: MySQL VIEW as performance troublemaker
August 12, 2007 By 53 Comments
…which uses VIEWs and was looking at the long running query which just joined 2 tables… I… case VIEW is simply expanded as a macro or Temporary Table in which case VIEW is …( `user_id` int(10) unsigned NOT NULL, `comment_id` int(10) unsigned NOT NULL, `message` text NOT NULL, PRIMARY KEY (`user…
Post: Recover BLOB fields
July 1, 2010 By 4 Comments
… TABLE `t1` ( `ID` int(11) unsigned NOT NULL, `NAME` varchar(120), `N_FIELDS` int(10), PRIMARY KEY (`ID`) ) ENGINE=… have offsets. The offset can be one or two bytes depending on maximum field size. …Percona InnoDB Recovery Tool supports now recovery of long fields. It is still in development branch, …
