June 19, 2013

Post: Implementing SchemaSpy in your MySQL environment

June 4, 2013 By 2 Comments

… Percona Server 5.6.10 sandbox: description=MySQL driver=com.mysql.jdbc.Driver connectionSpec=jdbc:mysql://127.0.0.1:5610/schemaspy driverPath… AUTO_INCREMENT, PRIMARY KEY (`parent_id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 CREATE TABLE `child_A` ( `id` int(10) unsigned NOT NULL…) unsigned DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 CREATE TABLE `child_B` ( `id` int(10) unsigned NOT NULL…

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

Post: The small improvements of MySQL 5.6: Duplicate Index Detection

May 31, 2013 By Leave a Comment

…`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 1 row in set (0.00 sec) In previous versions of MySQL, you can create two indexes… warning (0.56 sec) Records: 0 Duplicates: 0 Warnings: 1 mysql> SHOW WARNINGS\G *************************** 1. row *************************** Level: Note Code: 1831 Message: Duplicate…

Filed Under: Insight for DBAs, MySQL, On Ground Training, Percona Toolkit, Training Tagged With: , , , , , , , , ,

Post: How to recover table structure from InnoDB dictionary

April 22, 2013 By 4 Comments

…) unsigned DEFAULT NULL, PRIMARY KEY (`NAME`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 `NAME` is a human readable table name in form database… NULL, PRIMARY KEY (`TABLE_ID`,`POS`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 Here `TABLE_ID` is a well known table identifier, `POS…`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 Fields names explain their content. Index id in our example is 1679: mysql> SELECT * FROM SYS_FIELDS…

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

Post: Shard-Query EC2 images available

May 11, 2011 By 2 Comments

… `AirlineID` (`AirlineID`,`Carrier`), KEY `Carrier` (`Carrier`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT=’Contains information on flights, and what airline offered those…=InnoDB DEFAULT CHARSET=latin1 COMMENT=’Contains all avaialble data from 1988 to 2010′; mysql> use ontime1; Database changed mysql> show table status like…

Filed Under: MySQL

Post: Adventures in archiving

February 11, 2013 By 16 Comments

… following options for further testing. InnoDB’s inbuilt row compression MySQL‘s inbuilt compress() function leveraging zlib to provide compression of…_INCREMENT, `dat` blob, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; CREATE TABLE `compressed_row` ( `id` int(11) NOT NULL AUTO…

Filed Under: MySQL Tagged With: , ,

Post: Hijacking Innodb Foreign Keys

January 2, 2012 By 2 Comments

…) DEFAULT NULL, PRIMARY KEY (`parent_id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 CREATE TABLE `child` ( `parent_id` int(11) NOT NULL, `v… (`parent_id`) REFERENCES `parent` (`parent_id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 INSERT INTO parent(parent_id) VALUES(1,”"); INSERT INTO child…` (`parent_id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 1 row in set (0.00 sec) mysql> create table parentx(i int) engine=innodb…

Filed Under: Insight for DBAs, MySQL

Post: How fast can MySQL Process Data

April 9, 2008 By 3 Comments

… ) ENGINE=MEMORY DEFAULT CHARSET=latin1 mysql> select count(*) from m; +———-+ | count(*) | +———-+ | 1047684 | +———-+ 1 row in set (0.00 sec) mysql> select count(*) from… `m4` ( `c` char(128) NOT NULL ) ENGINE=MEMORY DEFAULT CHARSET=latin1 mysql> show table status like “m4″ \G *************************** 1. row *************************** Name: m4…

Filed Under: Benchmarks Tagged With:

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

March 4, 2013 By 7 Comments

… name, address, and the like. We are using MySQL 5.5.30 and MySQL 5.6.10 with no configuration tuning other… use the latin1 character set. mysql: CREATE TABLE innodb_ft_list2 ( value VARCHAR(18) NOT NULL DEFAULT ”) ENGINE=InnoDB DEFAULT CHARSET=latin1; Query OK, 0 rows affected (0.02 sec) mysql: SET GLOBAL innodb_ft_server_stopword…

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

Post: CentOS 5.8 users: your UTF-8 data is in peril with Perl MySQL

February 22, 2013 By 3 Comments

…-8 flag.  As far as Perl is concerned, this is Latin1 data. What does this mean for you?  In general, it… could corrupt the data by treating UTF-8 data as Latin1 data.  If the program doesn’t alter the data, then… Perl to access that data Have not upgraded DBD::mysql (perl-DBD-MySQL) then your UTF-8 data is in peril with…

Filed Under: Insight for Developers, Percona Toolkit Tagged With: , , , , ,

Post: The case for getting rid of duplicate “sets”

May 17, 2011 By 2 Comments

…=latin1 | +——-+—————————————————————————————————–+ 1 row in set (0.00 sec) mysql> select count(*) from ex1; +———-+ | count(*) | +———-+ | 73027220 | +———-+ 1 row in set (0.00 sec) mysql… NULL, `cnt` bigint(20) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 ; mysql> insert into search_set values (-2,1),(-3,1),(-10…

Filed Under: MySQL
« Older Posts