… suggest to change such fields to use ENUM column type, but is it really necessary (from performance standpoint)? In this post I’d like to present a small benchmark which shows MySQL performance when you use 3 different approaches: ENUM, VARCHAR and tinyint (+joined…
Post: Helgrinding MySQL with InnoDB for Synchronisation Errors, Fun and Profit
November 24, 2011 By 2 Comments
…to properly build the server to use it (bug 61587). We at Percona regularly use Memcheck for our development and upstream testing with useful…_array(THD*, char const*, st_mysql_show_var*, enum_var_type, system_status_var…_variables_hash. Deadlock is possible when one connection issues SHOW VARIABLES…
Post: Using MMM to ALTER huge tables
March 27, 2008 By 11 Comments
… to do certain table modifications online. It works well when all you want is to remove auto_increment or change ENUM values. When it comes to changes that really require table to be…’re not using MMM though it takes few more steps i.e. you must change application to use different IP for MySQL or ensure…
Post: MySQL opening .frm even when table is in table definition cache
November 21, 2011 By 8 Comments
…_definition_cache set to 1000. My understanding of this is that MySQL won’t revert to opening the FRM files to read the table…-offs from this work included being able to find out and then test that the ENUM limit of 65,535 has never… this is used is that in the DROP TABLE code path, MySQL uses this magic byte to work out which Storage Engine to ask to drop…
Post: Intro to OLAP
July 12, 2010 By 9 Comments
…to help performance using aggregation. Using materialized views to automate that aggregation process. Comparing the performance of OLAP with and without aggregation over multiple MySQL…such as the product, when it was sold and to whom it was… dimension is stored as an ENUM in many cases. In the…
Comment: How to find wrong indexing with glance view
August 23, 2008 By 27 Comments
…location, has_foto) 2) booleans – gender, has_foto; enums – available, looking_for, location; integer – age 3)… a little and give mysql a hint by using “gender IN (‘male’,'female’)” when people looking for … may be consider using (City, Age). Whatever you select to use can be used as prefix for other…
Comment: Enum Fields VS Varchar VS Int + Joined table: What is Faster?
January 24, 2008 By 27 Comments
Great to see some discussion and test results on this matter! In our application, we’re mostly using ENUM‘s for these kind of problems (mostly because table size really is an issue). In this comment I want to… with ENUM-fields; When we wanted to add a ‘type’ to an ENUM-field (or change the definition of the field in any way), MySQL often…
Post: Hacking to make ALTER TABLE online for certain changes
October 29, 2007 By 26 Comments
… not the way MySQL would advice you to do it and we…recommend to flush tables with read lock while switching .frm files. When and… does not work that way. Enum values (add and remove). …use the documented method for that. What I’m surprised about is that changing a comment does not to require table to…
Post: When the subselect runs faster
March 18, 2010 By 9 Comments
… This column in the table is looks like this: `col1` enum(‘A’,'B’,'C’,'CD’,'DE’,'F’,'G’,'HI’) default NULL… well) When you want to run this query mysql first will try to find each row where col1 is A or B using index. Then… subselect to make MySQL check WHERE clause while scanning table in index order. We can just use FORCE INDEX hint to override MySQL index choice: mysql…
Comment: COUNT(*) vs COUNT(col)
April 25, 2007 By 41 Comments
… data, not the row data for the table when you use a secondary index. Check the buffer pool …_karma` int(11) NOT NULL default ’0′, `comment_approved` enum(’0′,’1′,’spam’) NOT NULL default ’1′, `comment_… enough to use that for a count, even if it is not a unique index. For instance: mysql> explain …
