June 20, 2013

Post: How to recover table structure from InnoDB dictionary

April 22, 2013 By 4 Comments

… primary. CREATE TABLE `SYS_INDEXES` ( `TABLE_ID` bigint(20) unsigned NOT NULL DEFAULT ’0′, `ID` bigint(20) unsigned NOT NULL DEFAULT ’0′, `NAME` varchar… of the dictionary tables into some MySQL server. Use LOAD DATA INFILE constraints_parser generates mysql>LOAD DATA INFILE ‘/path/to/SYS…

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

Post: Benchmarking Percona Server TokuDB vs InnoDB

May 7, 2013 By 32 Comments

…, and the table looks like: CREATE TABLE sbtest$I ( id BIGINT UNSIGNED NOT NULL, k INTEGER UNSIGNED DEFAULT ’0′ NOT NULL… ’0′, `mid` int(10) unsigned NOT NULL DEFAULT ’0′, `id` bigint(20) unsigned NOT NULL, `k` int(10) unsigned NOT NULL DEFAULT… = 10G myisam_repair_threads = 1 myisam_recover socket=/var/lib/mysql/mysql.sock user=root skip-grant-tables TokuDB-related options are…

Filed Under: Benchmarks, MySQL, Percona Server Tagged With: , , ,

Post: Connecting orphaned .ibd files

May 13, 2011 By 19 Comments

…` bigint(20) unsigned NOT NULL default ’0′, `N_COLS` int(10) default NULL, `TYPE` int(10) unsigned default NULL, `MIX_ID` bigint(20) unsigned…_INDEXES: CREATE TABLE `SYS_INDEXES` ( `TABLE_ID` bigint(20) unsigned NOT NULL default ’0′, `ID` bigint(20) unsigned NOT NULL default ’0′, `NAME` varchar… the dictionary in ibdata1. # ./ibdconnect -o /var/lib/mysql/ibdata1 -f /var/lib/mysql/sakila/actor.ibd -d sakila -t actor actor…

Filed Under: MySQL

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

May 17, 2011 By 2 Comments

… `ex1` ( `val` bigint(20) NOT NULL DEFAULT ’0′ ) ENGINE=MyISAM DEFAULT CHARSET=latin1 | +——-+—————————————————————————————————–+ 1 row in set (0.00 sec) mysql> select count…: CREATE TABLE `search_set` ( `val` bigint(20) DEFAULT NULL, `cnt` bigint(20) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 ; mysql> insert into search_set values…

Filed Under: MySQL

Post: Checking the subset sum set problem with set processing

May 16, 2011 By Leave a Comment

… Shard-Query: CREATE TABLE `the list` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `val` bigint(20) NOT NULL DEFAULT ’0′, PRIMARY KEY (`id`), KEY… that I have a lot of numbers in my list: mysql> select val, count(*) from data group by val; +—–+———-+ | val | count… rows in set (20.47 sec) Now insert a value which will cause our check to pass: mysql> insert into data (val…

Filed Under: MySQL

Post: MySQL Partitioning - can save you or kill you

December 11, 2010 By 45 Comments

I wanted for a while to write about using MySQL Partitioning for Performance Optimization and I just got a relevant …: CREATE TABLE `tbl` ( `id` bigint(20) unsigned AUTO_INCREMENT NOT NULL, `uu` varchar(255) DEFAULT NULL, `data` bigint(20) unsigned DEFAULT NULL, PRIMARY KEY… reduced performance for random selects by UUID from 400 to 20 per second (from single thread). Decreasing number of partitions made…

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

Post: Logging Deadlock errors

September 19, 2012 By 4 Comments

…deadlocks` ( `server` char(20) NOT NULL, `ts` datetime NOT NULL, `thread` int(10) unsigned NOT NULL, `txn_id` bigint(20) unsigned NOT… we can check the deadlock information in our table: mysql> select * from test.deadlocks\G *************************** 1. row *************************** server: 127.0…

Filed Under: Insight for Developers, MySQL

Post: Talking MySQL to Sphinx

April 19, 2009 By 4 Comments

…ids in it, removing everything else: CREATE TABLE `sptest` ( `id` bigint(20) unsigned NOT NULL, `site_id` int(10) unsigned NOT …| 2735703 | 139448919 | 2735703 | +————+——–+———-+———–+———+ 10 rows in set (32.47 sec) MySQL mysql> select max(forum_id) as m,author_id as …

Filed Under: Insight for Developers Tagged With:

Comment: PHP vs. BIGINT vs. float conversion caveat

June 7, 2012 By 27 Comments

Great post! If you are using Mysql BIGINT(20) data type, read this twice.

Filed Under: Insight for Developers Tagged With: , ,

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

February 26, 2013 By 1 Comment

… -rw-rw—-. 1 mysql mysql 98304 Feb 20 16:09 FTS_00000000000000ad_DELETED.ibd -rw-rw—-. 1 mysql mysql 98304 Feb 20 16:09 FTS_00000000000000ad_STOPWORDS.ibd -rw-rw—-. 1 mysql mysql 98304 Feb 20 15:54 FTS_0000000000000114… lettercase, you’ll get an error: mysql> CREATE TABLE dir_test_innodb4 (fts_doc_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY…

Filed Under: Insight for DBAs, Insight for Developers, MySQL Tagged With: , , , , ,
« Older Posts