…planning to change allowed values for such field anyway. For status fields with more than two values …is also most inefficient one from performance standpoint. However how much of performance are you going to lose?…a discipline and have the table which has integer to string mapping or at least have …
Post: Using CHAR keys for joins, how much is the overhead ?
June 18, 2007 By 18 Comments
… start with first simple MyISAM table and join query performed on INT fields: CREATE TABLE `intjoin` ( `i` int(10) unsigned NOT NULL… keys. The performance dropped down to 6.1 seconds which makes it over 2 times slower compared to integer based join. So how do I read these results ? CHAR keys are indeed slower for joins compared to integer keys Performance…
Post: Enum Fields VS Varchar VS Int + Joined table: What is Faster?
January 24, 2008 By 30 Comments
… we suggest to change such fields to use ENUM column type, but is it really necessary (from performance standpoint)? In this post… 4th variant which is not comparable directly, which is using integer value and having value mapping done on application level. So… As you can see, ENUM and VARCHAR show close performance, while join performance degraded dramatically. Here is why: mysql> explain select SQL…
Post: Analyzing air traffic performance with InfoBright and MonetDB
October 2, 2009 By 50 Comments
…varchar(10) DEFAULT NULL ) ENGINE=BRIGHTHOUSE DEFAULT CHARSET=latin1; Last fields starting with “Div*” are not really used. Load procedure: … using c*1000/c2 here, because MonetDB seems using integer arithmetic and, with c/c2 I received just 1. … you want to compare or get info about air performance.
Post: MySQL-Memcached or NOSQL Tokyo Tyrant - part 1
October 15, 2009 By 16 Comments
…ensures that application is generally mean and lean and performs well. The issue is even this simple design … read a row from a database based on an integer based primary key B.) Update data from that … row in another table based on a text field ( called email address ). Seems simple enough right? My …
Comment: How to find wrong indexing with glance view
August 23, 2008 By 27 Comments
…) 2) booleans – gender, has_foto; enums – available, looking_for, location; integer – age 3) All queries fall into X IN (…) AND Y… used as prefix for other indexes cuz conditions on these fields are mandatory. 2) You’ve selected mandatory prefix, for example…’re smart enough and read mysql performance related articles then you’ll store all those fields as enums or tiny int with…
Post: INSERT ON DUPLICATE KEY UPDATE and summary counters.
May 29, 2006 By 48 Comments
… which can convert IP address strings to integers and back. This allows to save on field length significantly by using 4 bytes… The third feature this example takes advantage of is TIMESTAMP field. By default first TIMESTAMP column will have its value automatically… rows and data set size. 30% speedup should be typical. Performance increase is not the only benefit – what is even more…
Post: Common MySQL traps webinar questions followup
January 17, 2013 By 1 Comment
… in the cloud, and it is common that you prefer performance over data safety for a slave. So you may want… you make the field an ENUM(‘payed’,'shipped’,'canceled’,'archived’), the values will be stored internally as an integer and they will… book or practices. Of course I’m biased but High Performance MySQL 3rd edition is a very good book. The online…
Comment: How expensive is USER_STATISTICS?
June 2, 2012 By 14 Comments
… see any performance decrease when we activated this feature. The only limitation I found so far is that some fields (like user_statistics.bytes_received or user_statistics.rows_fetched) are stored as signed integer, so the max value of 2147483647 is rapidly reached. Not a big deal, but this requires you to perform FLUSH…
Comment: Analyzing air traffic performance with InfoBright and MonetDB
November 28, 2009 By 50 Comments
… post. What I did want to offer is that I performed the test on 10 years of data using the current… text strings to internal, integer lookup values. This improves load times, compression on those columns, and query performance when one of these… Code. When the same code has been used by multiple … — field for analysis across a range of years. AirlineID SMALLINT NOT…
