… total response time. Q: Do you have a recommendation on when to use ENUM vs. CHAR? Especially for one character values? Let’s… some subtleties of ENUMs due to their dual nature. Q: Are these recommendations true of all versions of MySQL? If not which… good command of SQL query writing and wanted to learn MySQL administration suggest any book or practices. Of course I’m…
Post: Helgrinding MySQL with InnoDB for Synchronisation Errors, Fun and Profit
November 24, 2011 By 2 Comments
…show_status_array(THD*, char const*, st_mysql_show_var*, enum_var_type, system_status_var*, char …LOCK_system_variables_hash. Deadlock is possible when one connection issues SHOW VARIABLES or …minor. Additionally, I’d consider backporting and using MySQL 5.5 atomic operation primitives with proper…
Post: Enum Fields VS Varchar VS Int + Joined table: What is Faster?
January 24, 2008 By 30 Comments
…”, etc, etc. Frequently we suggest to change such fields to use ENUM column type, but is it really necessary (from performance standpoint… which shows MySQL performance when you use 3 different approaches: ENUM, VARCHAR and tinyint (+joined table) columns. In practice you can also often use 4th…
Post: MySQL opening .frm even when table is in table definition cache
November 21, 2011 By 8 Comments
… being able to find out and then test that the ENUM limit of 65,535 has never been true (but now…) An example of how this is used is that in the DROP TABLE code path, MySQL uses this magic byte to work out… InnoDB itself and in the table_definition_cache). Further reading: MySQL Forge Internals document on the FRM file format Stewart’s…
Comment: How to find wrong indexing with glance view
August 23, 2008 By 27 Comments
…, looking_for, location, has_foto) 2) booleans – gender, has_foto; enums – available, looking_for, location; integer – age 3) All queries fall… multiple accesses by indexes. When we talk about index_merge_intersection, we should understand that mysql finds one set using an index, another… probably should cheat a little and give mysql a hint by using “gender IN (‘male’,'female’)” when people looking for any gender. Other…
Comment: Enum Fields VS Varchar VS Int + Joined table: What is Faster?
January 24, 2008 By 30 Comments
… results on this matter! In our application, we’re mostly using ENUM‘s for these kind of problems (mostly because table size…; i.e. using a TINYINT and doing the mapping on an application level. The problem we had 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…
Comment: How well does your table fits in innodb buffer pool ?
December 9, 2010 By 12 Comments
Error when using this request on MariaDB 5.2.5 110515 16:43:…_result(JOIN*, enum_schema_table_state)+0×211) [0x7fd959c64d41] /usr/sbin/mysqld(JOIN::exec()+0x4dd) [0x7fd959bca8ed] /usr/sbin/mysqld(mysql_select(THD…] /usr/sbin/mysqld(mysql_parse(THD*, char*, unsigned int, char const**)+0x2c1) [0x7fd959b606e1] /usr/sbin/mysqld(dispatch_command(enum_server_command, THD…
Post: Using MMM to ALTER huge tables
March 27, 2008 By 11 Comments
…It works well when all you want is to remove auto_increment or change ENUM values. When it comes to …and they must be executed on db2 so better use mysql file socket, or real IP of the node, … articles on our blog. When new changes are loaded: 4. on db2: start replication mysql> START SLAVE; On step…
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…) When you want to run this query mysql first will try to find each row where col1 is A or B using index… make MySQL check WHERE clause while scanning table in index order. We can just use FORCE INDEX hint to override MySQL index choice: mysql…
Post: PROCEDURE ANALYSE
March 23, 2009 By 9 Comments
… was originally defined: mysql> desc node; +———-+——————+——+—–+———+—————-+ | Field | Type | Null | Key | Default |…ENUM though as we know new types can appear by inserts we can’t do it. In other drupal installation though when… would not just use this tool …
