…you. Basically if your database has Foreign Keys then you’re laughing, if not don’t despair,…java/mysql-connector-java.jar Example Schema CREATE TABLE `parent` ( `parent_id` int(10) unsigned NOT NULL … – Names, number of children, parents, count of columns, row counts, and comments — a great way for…
Post: Is your MySQL buffer pool warm? Make it sweat!
April 16, 2013 By 6 Comments
… is not lagging. Unfortunately, that’s often not sufficient…mysql-username playback –mysql-password PaSSwOrd –mysql-schema schema_name –query-log-stdin –dispatcher-plugin thread-pool –thread-pool-threads-count…to redirect to /dev/null, and only have a…everything to be cached if we warmed the cache…
Comment: Derived Tables and Views Performance
May 21, 2013 By 16 Comments
…NOT NULL default ”, `link` varchar(100) NOT NULL default ‘#’, `parent` int(11) NOT NULL default ’0′, `sort` int(11) default NULL, PRIMARY KEY (`id`)) —————————————————————————————————— $mysql=mysql…
Post: Benchmarking Percona Server TokuDB vs InnoDB
May 7, 2013 By 32 Comments
… UNSIGNED NOT NULL, k INTEGER UNSIGNED DEFAULT ’0′ NOT NULL, c CHAR(120) DEFAULT ” NOT NULL, pad CHAR(60) DEFAULT ” NOT NULL… how both InnoDB and TokuDB performs if PK is (`id`,`hid`,`mid`). This…oltp-table-size=10000 –mysql-user=root –oltp-tables-count=32 –mysql_table_engine=tokudb –oltp_…
Post: Fun with the MySQL pager command
January 21, 2013 By 6 Comments
…: mysql> pager cat > /dev/null PAGER set to ‘cat > /dev/null‘ # Trying an execution plan mysql> SELECT…counting the rows from the output of SHOW PROCESSLIST is probably not the best solution. With pager, it is straightforward: mysql… can even write your custom script (if it is too complicated to fit…
Post: Wow. My 6 year old MySQL Bug is finally fixed in MySQL 5.6
August 30, 2012 By 2 Comments
…: If you perform match of constant which is too large to the column instead of simply responding with empty set MySQL truncates the constant, performs the lookup and only when discards results: CREATE TABLE `trunc` ( `i` int(11) NOT NULL default ’0′, KEY `i` (`i`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 mysql> select i,count(*) cnt from trunc group…
Post: MySQL Indexing Best Practices: Webinar Questions Followup
August 16, 2012 By 4 Comments
…TABLE LINK (id1 int unsigned not null ,id2 int unsigned not null, PRIMARY KEY(id1,id2),… a SELECT competition_id, COUNT(user_id) AS user_count FROM user_competition_…MySQL Server will not automatically define any indexes for you. Hopefully your CMS already comes with reasonable set of indexes, if not…
Post: How to convert MySQL's SHOW PROFILES into a real profile
February 20, 2012 By 12 Comments
… to demonstrate: mysql> SET profiling=1; mysql> pager cat > /dev/null mysql> SELECT * FROM …it’s sorted in execution order, not order of time consumption. Here …id ), 2) AS Pct_R, COUNT(*) AS Calls, SUM(DURATION) / COUNT(*) AS “R/Call” …the query’s real response time. If there were, I could add …
Post: MySQL 5.6 vs MySQL 5.5 and the Star Schema Benchmark
March 11, 2013 By 7 Comments
…IF NOT EXISTS lineorder ( LO_OrderKey bigint not null, LO_LineNumber tinyint not null, LO_CustKey int not null, LO_PartKey int not null, LO_SuppKey int not null, LO_OrderDateKey int not null…
Post: MySQL VIEW as performance troublemaker
August 12, 2007 By 57 Comments
… `comments` ( `user_id` int(10) unsigned NOT NULL, `comment_id` int(10) unsigned NOT NULL, `message` text NOT NULL, PRIMARY KEY (`user_id`,`comment_id`) ) ENGINE… counts for each user and can simply query from that table restricting by user_id. If this would be handled properly inside MySQL…
