June 20, 2013

Post: MyISAM Scalability and Innodb, Falcon Benchmarks

October 12, 2007 By 10 Comments

… select_type: SIMPLE table: t1 type: eq_ref possible_keys: PRIMARY,id key: PRIMARY key_len: 4 ref: scale.t2.t1_id rows: 1 Extra… NOT NULL auto_increment, `stat` int(11) unsigned NOT NULL, PRIMARY KEY (`id`), KEY `id` (`id`,`stat`) ) ENGINE=MyISAM; CREATE TABLE `t2` ( `id… on Key Read Request. This lock is per key cache so if you have contention while multiple indexes are used you can create multiple key…

Filed Under: Uncategorized Tagged With: , , , ,

Post: Falcon Storage Engine Design Review

January 12, 2007 By 19 Comments

… support Unlike Innodb Falcon does not cluster data by Primary Key or any other key. This is good for some applications bad for… its internal hidden primary key) but this functionality is not exported well as it requires you to get rid of primary keys etc. Some… it can be the problem – if transaction spawns multiple databases you need multiple log flushes on commit, plus ether you use XA…

Filed Under: Insight for DBAs Tagged With:

Post: ORDER BY ... LIMIT Performance Optimization

September 1, 2006 By 100 Comments

… be developed, but if we stick to simple MySQL using multiple indexes on most selective columns would be good idea for… | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +—-+————-+——-+——-+—————+———+———+——-+——+————-+ | 1 | SIMPLE | test | const | PRIMARY,k | PRIMARY | 4 | const | 1 | | | 1 | SIMPLE….01 sec) The difference between these cases is “i” is primary key while “k” is simply indexes column. Note: In some cases…

Filed Under: Insight for Developers Tagged With: ,

Post: MySQL 5.6 vs MySQL 5.5 and the Star Schema Benchmark

March 11, 2013 By 7 Comments

…_blocks_time=1000 on MySQL 5.5.30 and testing multiple buffer pools on MySQL 5.5.30. Finally, MySQL 5… select_type: SIMPLE table: dim_date type: eq_ref possible_keys: PRIMARY key: PRIMARY key_len: 4 ref: ssb.lineorder.LO_OrderDateKey rows: 1 Extra…), key(P_Name), key(P_MFGR), key(P_Category), key(P_Brand) ); DROP TABLE IF EXISTS supplier; CREATE TABLE supplier ( S_SuppKey int primary key, S…

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

Post: A case for MariaDB's Hash Joins

May 31, 2012 By Leave a Comment

… on it is greater than the join_buffer_size, then multiple hash tables would be created. For example if the left… NULL, PRIMARY KEY (`l_orderkey`,`l_linenumber`), KEY `i_l_shipdate` (`l_shipDATE`), KEY `i_l_suppkey_partkey` (`l_partkey`,`l_suppkey`), KEY `i_l… NULL, `o_comment` varchar(79) DEFAULT NULL, PRIMARY KEY (`o_orderkey`), KEY `i_o_orderdate` (`o_orderDATE`), KEY `i_o_custkey` (`o_custkey`) ) ENGINE…

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

Comment: Why MySQL could be slow with large tables ?

January 31, 2008 By 141 Comments

… intended, but INSERTing in them is a nightmare. I use multiple record INSERTs (150 each time), and the first INSERT usually…(3) UNSIGNED NOT NULL DEFAULT ’0′, PRIMARY KEY (A,B,C,D), KEY (E), CONSTRAINT key_A FOREIGN KEY (A) REFERENCES ATable(A) ON DELETE… the size of the PRIMARY KEY?! Could it be faster if I’d just assigned a different [FOREIGNER] KEY for every capital letter…

Filed Under: Insight for DBAs Tagged With: ,

Post: Find and remove duplicate indexes

June 20, 2012 By 9 Comments

… and remove them. Duplicate keys on the same column This is the easiest one. You can create multiple indexes on the same…> show create table t; [...] PRIMARY KEY (`i`), KEY `key_name` (`name`,`i`) In this example the index ‘key_name’ includes the primary key so that last column… create table t; [...] PRIMARY KEY (`i`), KEY `name` (`name`,`i`), KEY `name_2` (`name`), KEY `name_3` (`name`), KEY `name_4` (`name`,`age`), KEY `age` (`age`,`name…

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

Post: Duplicate indexes and redundant indexes

August 17, 2006 By 38 Comments

… see something like PRIMARY KEY(id), UNIQUE KEY id(id), KEY id2(id) The logic I heard behind this often – create primary key as object identifier, now we create UNIQUE because we want it to be UNIQUE and we create KEY so it… create PRIMARY KEY and it will enforce unique values and will be used in the queries. The other case is simply having multiple keys…

Filed Under: Insight for DBAs Tagged With:

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

February 26, 2013 By 1 Comment

… NULL, `full_name` varchar(100) DEFAULT NULL, `details` text, PRIMARY KEY (`id`), FULLTEXT KEY `full_name` (`full_name`,`details`) ) ENGINE=InnoDB DEFAULT CHARSET… and dropping FT indexes, it’s entirely possible to DROP multiple FT indexes with InnoDB in the same ALTER TABLE statement…

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

Post: Why Index could refuse to work ?

September 8, 2006 By 15 Comments

…(20) NOT NULL, `dummy` varchar(255) NOT NULL default ‘dummy’, PRIMARY KEY (`article_id`) ) As you can see article_id is VARCHAR… | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +—-+————-+———+——+—————+———+———+——-+——+————-+ | 1 | SIMPLE | article | ref | PRIMARY | PRIMARY | 62 | const | 1 | Using where… wrong result for some queries. The thing is there are multiple strings possible for single integer value – for example for there…

Filed Under: Insight for DBAs Tagged With:
« Older Posts
Newer Posts »