…Fulltext, Memcached integration, a more complete performance schema, online DDL and several…varchar(200) default null # To remove this duplicate index, execute: ALTER TABLE `test`.`test` DROP INDEX `col2`; # ######################################################################## # Summary of indexes…
Post: Improved InnoDB fast index creation
November 6, 2011 By 4 Comments
… see how performance is affected when turning expand_fast_index_creation on. Here and in later examples I’m extending the VARCHAR column to trigger table rebuilds without affecting the table size. mysql> SET expand_fast_index_creation=ON; Query… does not fit in the buffer pool, fast index creation provides even better performance as compared to the default method, because it…
Post: Enum Fields VS Varchar VS Int + Joined table: What is Faster?
January 24, 2008 By 30 Comments
… present a small benchmark which shows MySQL performance when you use 3 different approaches: ENUM, VARCHAR and tinyint (+joined table) columns. In…): 26.0854793 As you can see, ENUM and VARCHAR show close performance, while join performance degraded dramatically. Here is why: mysql> explain select… amount of rows full table scan performs about 25 times better than accessing rows via index (for the case when data fits…
Post: Redundant index is not always bad
August 28, 2007 By 7 Comments
… WHERE A=5 the index (A,B) also can be used. But there is case when for performance it would be good…-compression for VARCHAR columns, so makes things worse). So to have good performance for both case we should leave both indexes: KEY `state… I limited available memory to fit only one index, that shows how can degrade performance if we balance on memory limits) If…
Post: MySQL Indexing Best Practices: Webinar Questions Followup
August 16, 2012 By 4 Comments
… should worry about performance impact of extending index is when you increase its length dramatically, for example adding long varchar column. In such… of indexes I prefer to add indexes only in cases which provides positive impact to performance. At some point the gains from indexes you…
Post: Why Index could refuse to work ?
September 8, 2006 By 15 Comments
… at article table: CREATE TABLE `article` ( `article_id` varchar(20) NOT NULL, `dummy` varchar(255) NOT NULL default ‘dummy’, PRIMARY KEY (`article… article_id is VARCHAR and this is the problem. Comparing String to Number is not going to use the index. Lets check… why MySQL can’t use index in this case, simply by converting number to the string and performing index lookup ? This can’t…
Post: Analyzing air traffic performance with InfoBright and MonetDB
October 2, 2009 By 50 Comments
…varchar(10) DEFAULT NULL, `TotalAddGTime` varchar(10) DEFAULT NULL, `LongestAddGTime` varchar(10) DEFAULT NULL, `DivAirportLandings` varchar(10) DEFAULT NULL, `DivReachedDest` varchar(10) DEFAULT NULL, `DivActualElapsedTime` varchar…also do not have indexes like InfoBright, but results are…performance…
Post: How number of columns affects performance ?
September 28, 2009 By 12 Comments
…) unsigned NOT NULL, … `t99` tinyint(3) unsigned NOT NULL, `v1` varchar(1) NOT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 I populated…: CREATE TABLE `t1v1` ( `t1` tinyint(3) unsigned NOT NULL, `v` varchar(1) NOT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 This table… expected as table data is stored in BTREE index very similarly to how indexes are stored. Summary: Beware of dynamic row format…
Post: JOIN Performance & Charsets
October 21, 2008 By 2 Comments
…can have a significant impact on the performance of your queries. Take the following example…t1` ( `char_id` char(6) NOT NULL, `v` varchar(128) NOT NULL, PRIMARY KEY (`char_id`) … *************************** id: 1 select_type: SIMPLE table: t1 type: index possible_keys: PRIMARY key: PRIMARY key_len:…
Post: Multi Column indexes vs Index Merge
September 19, 2009 By 31 Comments
…) unsigned NOT NULL, `i2` int(10) unsigned NOT NULL, `val` varchar(40) DEFAULT NULL, KEY `i1` (`i1`), KEY `i2` (`i2`), KEY… such columns in where clause. Index merge does helps performance but it is far from performance of combined index in this case. In case you’re using OR between columns – single column indexes are required for index merge…
