…. The results below are for MySQL 5.1.18 using MyISAM and Innodb tables. …char(10) DEFAULT NULL, `j` int(10) unsigned NOT NULL, KEY `i` (`i`), KEY `j` (`j`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 mysql> select sum(t1.i+t2.j+length(t2.c)+t1.j) …
Post: How fast can MySQL Process Data
April 9, 2008 By 3 Comments
…mysql> select count(*) from m; +———-+ | count(*) | +———-+ | 1047684 | +———-+ 1 row in set (0.00 sec) mysql> select…char(128) NOT NULL ) ENGINE=MEMORY DEFAULT CHARSET=latin1 mysql…length: 129 Data_length: 685609952 Max_data_length: 948528873 Index_length…
Post: Efficient Boolean value storage for Innodb Tables
April 23, 2008 By 6 Comments
…length: 11 Data_length: 23068672 Max_data_length: 3096224743817215 Index_length…Using NULL as one of flag values means you can’t use normal “=” comparison operator with them: mysql> select…
Post: Shard-Query EC2 images available
May 11, 2011 By 2 Comments
…` tinyint(4) DEFAULT NULL, `CancellationCode` char(1) DEFAULT NULL, `Diverted` tinyint… to 2010′; mysql> use ontime1; Database changed mysql> show table …_row_length: 241 Data_length: 1616904192 Max_data_length: 0 Index_length:…use are shown in the EC2 console. That the “private ip” field is selected…
Post: Multiple column index vs multiple indexes
August 22, 2008 By 13 Comments
…) NOT NULL, `j` int(11) NOT NULL, `val` char(10) NOT NULL, KEY `i` (`i`,`j`) ) …using where clause which covers multiple columns: Q1 SELECT sum(length(val)) FROM T WHERE i=2 AND j=1 Q2 SELECT sum(length…cardinality even though MySQL can’t use index well. You’re right MySQL can’t and MySQL does not – …
Post: Moving Subtrees in Closure Table Hierarchies
February 14, 2011 By 18 Comments
… textbook solutions. CREATE TABLE TreePaths ( ancestor CHAR(1) NOT NULL, descendant CHAR(1) NOT NULL, length INT NOT NULL DEFAULT 0, PRIMARY KEY… can’t DELETE and SELECT from the same table in a single query in MySQL. But we can use MySQL‘s multi-table DELETE… (going down). INSERT INTO TreePaths (ancestor, descendant, length) SELECT supertree.ancestor, subtree.descendant, supertree.length+subtree.length+1 FROM TreePaths AS supertree JOIN TreePaths…
Post: PROCEDURE ANALYSE
March 23, 2009 By 9 Comments
… mysql> select * from node procedure analyse() \G *************************** 1. row *************************** Field_name: percona_drupal.node.nid Min_value: 1 Max_value: 194 Min_length…same width like hashes CHAR can be more efficient storage. In general even though I would not just use this tool …
Post: Covering index and prefix indexes
November 23, 2006 By 5 Comments
…, `j` char(10…Using Index” in Extra column. mysql> explain select j,k from t where i=5 \G *************************** 1. row *************************** id: 1 select…use length longer than column length, lets say 20, MySQL will refuse to create an index. So in general handling of prefix keys in MySQL…
Post: How is join_buffer_size allocated?
July 5, 2010 By 9 Comments
…MySQL configuration, we quite often want to know how various buffer sizes are used. …table_count) 14185 { … snip … 14268 cache->length=length+blobs*sizeof(char*); 14269 cache->blobs=blobs; 14270 *blob_… << 30; # 1GB select * from (select 1 union select 1) as x1 join (select 1 union select 1) as …
Post: Memory allocation in Stored Function
August 26, 2007 By 8 Comments
…’ve found in MySQL manual: CREATE FUNCTION hello (s CHAR(20)) RETURNS CHAR(50) RETURN CONCAT(‘Hello, ‘,s,’!'); Originally I used UPDATE statement, but for avoiding I/O and allocating os cashes let’s check only select: mysql> select avg(length(hello(name…
