…GROUP BY parent) Deriv1 ON a.id = Deriv1.parent WHERE a.parent=” . $parent); echo “”; while ($row = mysql_fetch_assoc($result)) { if…
Post: Benchmarking Percona Server TokuDB vs InnoDB
May 7, 2013 By 32 Comments
…NULL, `k` int(10) unsigned NOT NULL DEFAULT ’0′, PRIMARY KEY (`hid`,`mid`,`id`) ) and transactions are: $HID=rand(0,10000) // distributed by… measure that also. And, if you want to repeat…= 4G innodb_log_files_in_group = 2 innodb_log_block…1 myisam_recover socket=/var/lib/mysql/mysql.sock user=root …
Post: Using GROUP BY WITH ROLLUP for Reporting Performance Optimization
September 17, 2007 By 14 Comments
… as number of groups is relatively small: mysql> select grp, count(*) cnt from dt where slack like “a%” group by grp order by null; +——-+—–+ | grp | cnt… slower than standard group by even though the only thing it needs to do is maintain an extra count. So if MySQL does not allow us to use ORDER BY together with GROUP BY WITH ROLLUP can we still do…
Post: Eventual Consistency in MySQL
November 18, 2011 By 8 Comments
… foreign keys. If the REFERENCED_* columns are non-null, it’s a foreign key. mysql> SELECT * FROM… ‘PRIMARY’ WHERE K.REFERENCED_TABLE_NAME IS NOT NULL GROUP BY K.CONSTRAINT_NAME; …
Post: MySQL Indexing Best Practices: Webinar Questions Followup
August 16, 2012 By 4 Comments
… like CREATE TABLE LINK (id1 int unsigned not null ,id2 int unsigned not null, PRIMARY KEY(id1,id2), KEY K(id2)) engine…: how mysql use index for group by? A: If you have Index on the column MySQL can avoid temporary table or filesort for group by by this column. This works because by…
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>…: mysql> SELECT COMMAND,COUNT(*) TOTAL FROM INFORMATION_SCHEMA.PROCESSLIST GROUP BY COMMAND ORDER BY TOTAL DESC; +————-+——-+ | COMMAND |… even write your custom script (if it is too complicated to…
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 by i order by cnt…
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 …WHERE QUERY_ID = @query_id GROUP BY STATE ORDER BY Total_R DESC; +———————-+———-+——-+——-+————–+ | STATE | Total_R …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
…for MySQL 5.5 and MySQL 5.6, no tuning at all Config 2: MySQL 5…group by d_year, s_city, p_brand order by d_year, s_city, p_brand; And the schema: DROP TABLE IF…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: Flexviews - part 3 - improving query performance using materialized views
April 4, 2011 By 20 Comments
…the dashboard_customer_sales table. If you were paying attention to … as ol USING (order_id) -> GROUP BY (customer_id) -> ORDER BY total_price desc -> LIMIT 10; +————-+————-+————-+… mysql> call flexviews.refresh( -> flexviews.get_id(‘demo’,'complete_example2′),’BOTH’,NULL); …
