… SELECT queries you can be potentially waiting for hours for this statement to complete. Here is example how it can look: mysql… | dumptest | Query | 324 | Sending data | select count(*) from A,B | 0 | 0 | 2359297 | | 10290 | root | localhost | NULL | Query | 317 | Waiting for table… all (use mysql -A in this case, to prevent it from getting field list from all tables) Reads though would not be blocked…
Post: Enum Fields VS Varchar VS Int + Joined table: What is Faster?
January 24, 2008 By 27 Comments
…see a huge tables with varchar/char fields, with small sets of possible values. These are… int(10) unsigned NOT NULL auto_increment, state varchar(50) NOT NULL, city varchar(255) NOT NULL, PRIMARY KEY (id),…performance degraded dramatically. Here is why: mysql> explain select SQL_NO_CACHE c.city from …
Post: Connecting orphaned .ibd files
May 13, 2011 By 12 Comments
… DEFAULT CHARSET=latin1 Please note field SPACE. For table actor it is equal to 15: mysql> select * from `INNODB_SYS_TABLES` …mysql>CREATE TABLE actor ( actor_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT, first_name VARCHAR(45) NOT NULL, last_name VARCHAR(45) NOT NULL, last_update TIMESTAMP NOT NULL…
Post: Shard-Query EC2 images available
May 11, 2011 By 2 Comments
…NULL, `Month` tinyint(4) DEFAULT NULL, `DayofMonth` tinyint(4) DEFAULT NULL, `DayOfWeek` tinyint(4) DEFAULT NULL, `FlightDate` date NOT NULL, `date_id` smallint(6) NOT NULL…field is selected in the list of columns to show (click show/hide to change the columns) That the “public dns” field is selected…
Post: Ultimate MySQL variable and status reference list
January 8, 2011 By 4 Comments
…amazing MySQL manual, especially the option and …_lowmem_prunesblogpercona.commanual Qcache_not_cachedblogpercona.commanual Qcache_queries_…Select_full_joinblogpercona.commanual Select_full_range_joinblogpercona.commanual Select_rangeblogpercona.commanual Select_range_checkblogpercona.commanual Select…
Post: PROCEDURE ANALYSE
March 23, 2009 By 9 Comments
… rows. mysql> select * from node procedure analyse() \G *************************** 1. row *************************** Field_name…’) NOT NULL Here is how this table was originally defined: mysql> desc node; +———-+——————+——+—–+———+—————-+ | Field | Type | Null …
Comment: Innodb vs MySQL index counts
November 30, 2011 By 6 Comments
…field list’ > insert into test( id, data ) VALUES (3, 1001 ); Query OK, 1 row affected (0.01 sec) > select …` ( `id` int(10) unsigned NOT NULL, `data` varchar(100) DEFAULT NULL, `foo` varchar(20) DEFAULT NULL, PRIMARY KEY (`id`) ) … in the mysqld.err. (this was on vanilla MySQL 5.5) Let’s see if the …
Post: Efficient Boolean value storage for Innodb Tables
April 23, 2008 By 6 Comments
…fields which get combined together for efficient storage: CREATE TABLE `bbool` ( `b1` bit(1) NOT NULL, `b2` bit(1) NOT NULL, `b3` bit(1) NOT NULL, … based on parameter (IS ” would not work) or you can use Null-Aware comparison operator: mysql> select count(*) from cbool where c1NULL; +———-+ | …
Comment: COUNT(*) for Innodb Tables
December 20, 2011 By 29 Comments
…Field | Type | Null | Key | Default | Extra | +—————+————–+——+—–+———+—————-+ | id | bigint(20) | NO | PRI | NULL | auto_increment | | id_acct | int(11) | NO | MUL | 0 | | mysql> select…
Post: Debugging problems with row based replication
May 6, 2010 By 10 Comments
…MySQL master database and populate it with some data. CREATE TABLE `t1` ( `c1` int(11) NOT NULL…select NULL from t1; Query OK, 2 rows affected (0.01 sec) Records: 2Â Duplicates: 0Â Warnings: 0 … insert into repl.t1 select NULL… fields omitted): SHOW SLAVE STATUS\G Relay_Log_File: mysql_…
