… (City, Gender, Age, LookingFor) and (City, Gender, Age, Available). So mysql can choose between two which is more selective. 3) You…’re smart enough and read mysql performance related articles then you’ll store all those fields as enums or tiny int with…/using-union-to-implement-loose-index-scan-to-mysql/#comment-1695 2. http://dev.mysql.com/doc/refman/5.1/en/index…
Comment: How well does your table fits in innodb buffer pool ?
December 9, 2010 By 12 Comments
…_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: Efficient Boolean value storage for Innodb Tables
April 23, 2008 By 6 Comments
… `c10` char(0) default NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1 mysql> show table status like “%bool%” \G *************************** 1. row *************************** Name: bbool…think so – for most applications using TINYINT BIT(1) or ENUM for flags benefit would unlikely be worth the trouble. …
Post: Statistics of InnoDB tables and indexes available in xtrabackup
September 14, 2009 By 6 Comments
… NULL, `message_published` timestamp NOT NULL default CURRENT_TIMESTAMP, `kind` enum(‘link’,'img’) NOT NULL, `url_title` varchar(255) NOT NULL… we run: xtrabackup –stats –tables=art.link* –datadir=/mnt/data/mysql/ which will show something like this: table: art/link_out104… the post – if you are badly missing some features in MySQL, InnoDB, InnoDB-plugin, XtraDB, XtraBackup – you know whom ask for…
Post: Using MMM to ALTER huge tables
March 27, 2008 By 11 Comments
… all you want is to remove auto_increment or change ENUM values. When it comes to changes that really require table… indexes, changing data type, converting data to different character set – MySQL master-master replication especially accompanied by MMM can be very… one of our MySQL support customers as they were upgrading their application and mysql schema. We deployed and used MySQL Master-Master replication…
Comment: COUNT(*) vs COUNT(col)
April 25, 2007 By 45 Comments
… pages 18664 Modified db pages 0 restart mysql and check the count of (rajid): mysql> select count(rajid) from wp_comments; +————–+ | count…, `comment_karma` int(11) NOT NULL default ’0′, `comment_approved` enum(’0′,’1′,’spam’) NOT NULL default ’1′, `comment_agent` varchar… row in set (0.23 sec) mysql> select count(*) from wp_comments; ERROR 2006 (HY000): MySQL server has gone away No connection…
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…. We can just use FORCE INDEX hint to override MySQL index choice: mysql> explain select * from table FORCE INDEX(PRIMARY) where (col1… | SIMPLE | table | index | NULL | PRIMARY | 4 | NULL | 549117 | Using where | +—-+————-+——-+——-+—————+———+———+——+——–+————-+ mysql> select * from table FORCE INDEX(PRIMARY) where (col1=’A'||col1…
Post: Intro to OLAP
July 12, 2010 By 10 Comments
… intelligence tools, particularly OLAP (or online analytical processing) tools using MySQL and other free open source software. OLAP tools are a… the performance of OLAP with and without aggregation over multiple MySQL storage engines at various data scales. What is BI? Chances… a degenerate dimension. A degenerate dimension is stored as an ENUM in many cases. In the example below that there is…
Post: Idea: Couple of more string types
April 18, 2008 By 12 Comments
MySQL has a lot of string data types – CHAR, VARCHAR, BLOB, TEXT, ENUM and bunch of variants such as VARBINARY but I… is quite inconvenient to deal with strings like that in MySQL. Either you store them as strings and waste space or…. Though this is likely to require more significant changes in MySQL so I would not expect to happen quickly. The basic…
Post: Lost innodb tables, xfs and binary grep
November 9, 2010 By 12 Comments
…, some facts about the system and how data was lost: MySQL had a dedicated partition on XFS file system Server was…: 1. There was this rather big table with integer and enum columns only, where we knew a rather unique PK, well…
