…of the most common questions we hear is how to build high-performance systems to do…int unsigned not null, is_blue tinyint unsigned not null, clicks int unsigned not null, impressions int unsigned not null, …. primary key(day, ad, is…’re okay with long queries that read a lot of rows — MySQL can’t …
Post: Helgrinding MySQL with InnoDB for Synchronisation Errors, Fun and Profit
November 24, 2011 By 2 Comments
… most out of Memcheck. Not everything is perfect though: the manual is only partially correct in how to properly build the server to… 0x6A7D72: mysql_select(THD*, Item***, TABLE_LIST*, unsigned int, List
Post: MySQL VIEW as performance troublemaker
August 12, 2007 By 53 Comments
…int(10) unsigned NOT NULL, `message` text NOT NULL, PRIMARY KEY (`user_id`,`comment_id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 So how…. Unfortunately it does not work. It is interesting to see EXPLAIN for such …in case they are used with caution. MySQL has long way to go getting queries with VIEWs…
Post: Using CHAR keys for joins, how much is the overhead ?
June 18, 2007 By 18 Comments
… int(10) unsigned NOT NULL, KEY `i` (`i`), KEY `j` (`j`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 mysql>…slower compared to integer based join. So how do I read these results ? CHAR …is not disabled Joining on Shorter CHAR keys is significantly faster than Long keys Latin1 (I guess any simple encoding) is…
Post: How to use tcpdump on very busy hosts
April 18, 2011 By 17 Comments
… all queries which it believes took longer than the servers long-query-time of 1 second tcpdump traffic only from a… from mysqld) #1 has an obvious flaw — if your long-query-time is 1 second, and mkqd believes that a query which… $LOCKFILE; kill $PID; ssh $REMOTEHOST — “rm -f $TMP/$TMPfile”; exit’ INT TERM EXIT touch $TMP/$LOCKFILE # run the tcpdump & write to…
Post: How much memory Innodb locks really take ?
July 13, 2006 By 11 Comments
… how it looks in practice: I used the same sample table as yesterday: CREATE TABLE `sample` ( `i` int(10… and see how long does it take: mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> select count(i… on modern systems. So we have tested how much memory is using locking all the rows – so all…
Post: How much overhead is caused by on disk temporary tables
August 16, 2007 By 26 Comments
…( `i` int(10) unsigned NOT NULL, `c` char(50) NOT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 mysql> select…second to do, but it is not how it happens in practice. mysql> show global status like “key%”; …rows in set (0.00 sec) So how long does it take: mysql> select count(*) cnt,c from gt…
Post: Moving Subtrees in Closure Table Hierarchies
February 14, 2011 By 7 Comments
…how long. We even store paths of length zero, which means a node is its own parent. So if A is a…1) NOT NULL, descendant CHAR(1) NOT NULL, length INT NOT NULL DEFAULT 0, PRIMARY KEY (ancestor, descendant) ) …same table in a single query in MySQL. But we can use MySQL‘s multi-table DELETE syntax, to …
Post: To SQL_CALC_FOUND_ROWS or not to SQL_CALC_FOUND_ROWS?
August 28, 2007 By 53 Comments
…: CREATE TABLE `count_test` ( `a` int(10) NOT NULL auto_increment, `b` int(10) NOT NULL, `c` int(10) NOT NULL, `d` varchar… clause. Let’s check, how long it’d take if we’ll try to use two separate queries: mysql> SELECT SQL_NO_CACHE… too check how long our COUNT query would take: mysql> SELECT SQL_NO_CACHE count(*) FROM count_test WHERE b = 666; Result is really…
Post: Connecting orphaned .ibd files
May 13, 2011 By 12 Comments
… NULL, `N_FIELDS` int(10) unsigned default NULL, `TYPE` int(10) unsigned default NULL, `SPACE` int(10) unsigned default …of a set of InnoDB pages. A page is 16k long (UNIV_PAGE_SIZE in the source code…every InnoDB page. How let’s connect actor.ibd from sakila database taken from some MySQL server. 0….
