June 19, 2013

Post: Connecting orphaned .ibd files

May 13, 2011 By 19 Comments

…TABLES: CREATE TABLE `SYS_TABLES` ( `NAME` varchar(255) NOT NULL default ”, `ID` bigint(20) unsigned NOT NULL default ’0′, `N_COLS` int(10) default NULL, `TYPE… database taken from some MySQL server. 0. Create empty InnoDB tablespace. 1. Create the table: mysql>CREATE TABLE actor ( actor_id SMALLINT…

Filed Under: MySQL

Post: InnoDB Full-text Search in MySQL 5.6: Part 2, The Queries!

March 4, 2013 By 7 Comments

…mysql: SHOW CREATE TABLE information_schema.innodb_ft_default_stopword\G *************************** 1. row *************************** Table: INNODB_FT_DEFAULT_STOPWORD Create Table: CREATE TEMPORARY TABLE `INNODB_FT_DEFAULT… tables, it simply mentions the table engine and the column name/type…

Filed Under: Insight for DBAs, Insight for Developers, MySQL Tagged With: , , , , ,

Post: MySQL 5.5 and MySQL 5.6 default variable values differences

February 21, 2013 By 8 Comments

… the values from MySQL 5.5.30 and MySQL 5.6.10 to the different tables and ran the query: mysql [localhost] {msandbox} (test… default still but it is achieved differently now. The query_cache_type is now off by default with default size of 1MB while in MySQL 5.5 and before it was “ON” by default with…

Filed Under: Insight for DBAs, MySQL Tagged With: , , ,

Post: Efficient Boolean value storage for Innodb Tables

April 23, 2008 By 6 Comments

… TABLE `cbool` ( `c1` char(0) default NULL, `c2` char(0) default NULL, `c3` char(0) default NULL, `c4` char(0) default NULL, `c5` char(0) default…(0) default NULL, `c8` char(0) default NULL, `c9` char(0) default NULL, `c10` char(0) default NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1 mysql> show table status… (0.11 sec) As you can see table which uses BIT(1) column type takes same space as the one which uses…

Filed Under: Insight for Developers Tagged With:

Post: InnoDB Full-text Search in MySQL 5.6 (part 1)

February 26, 2013 By 1 Comment

…table_name | | | use_stopword | 1 | | table_state | 0 | +—————————+———+ 10 rows in set (0.02 sec) mysql> optimize table dir_test_innodb; +———————-+———-+———-+———-+ | Table | Op | Msg_type…

Filed Under: Insight for DBAs, Insight for Developers, MySQL Tagged With: , , , , ,

Post: Recovering CREATE TABLE statement from .frm file

December 17, 2008 By 5 Comments

…MySQL Community Server (GPL) Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the buffer. mysql> show create table test_innodb; +————-+—————————————————————————————————————————-+ | Table | Create Table…

Filed Under: Insight for DBAs Tagged With: , ,

Post: Extending Index for Innodb tables can hurt performance in a surprising way

May 22, 2010 By 10 Comments

…a`) ) ENGINE=InnoDB AUTO_INCREMENT=6029313 DEFAULT CHARSET=latin1 mysql> select count(*) from …mysql> explain select count(*) from idxitest where a=5 and b=5; +—-+————-+———-+——+—————+——+———+————-+——–+————-+ | id | select_type | table | type…

Filed Under: Insight for DBAs, MySQL Tagged With: ,

Post: pt-online-schema-change and default values

June 21, 2012 By 1 Comment

… ALTER TABLE in MySQL I can ignore default value and it will be assigned based on the column type. For example this alter table sbtest add column v varchar(100) not null would work even though we do not specify default value. MySQL will…

Filed Under: Insight for DBAs, MySQL

Post: MySQL 5.6.10 Optimizer Limitations: Index Condition Pushdown

March 14, 2013 By 2 Comments

…`,`note`) ) ENGINE=InnoDB AUTO_INCREMENT=22187769 DEFAULT CHARSET=utf8; The table had 22 million rows, with approximately …mysql> EXPLAIN SELECT * FROM cast_info WHERE role_id = 1 and note like ‘%Jaime%’\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table…

Filed Under: Events and Announcements, Insight for Developers, MySQL Tagged With: , ,

Post: Can MySQL temporary tables be made safe for statement-based replication?

May 26, 2008 By 8 Comments

… Last_Errno: 1146 Last_Error: Error ‘Table ‘test.t’ doesn’t exist’ on query. Default database: ”. Query: ‘insert into test.t(… use `test`; drop temporary table test.t *************************** 7. row *************************** Log_name: mysql-bin.000011 Pos: 567 Event_type: Xid Server_id: 1 …

Filed Under: Insight for DBAs Tagged With:
« Older Posts
Newer Posts »