… o_orderdate < date_add( ’1993-08-01′ ,interval ’3′ month) and l_returnflag = ‘R’ and c_nationkey = n_nationkey group by c_custkey, c_name, c_acctbal, c_phone, n_name, c_address, c_comment order by revenue desc LIMIT… MySQL 5.5, as can be seen by the numbers reported for the status counter Innodb_rows_read. While MySQL 5.6 is also reporting…
Post: What would make MySQL Multiple Queries Usable ?
September 5, 2007 By 1 Comment
…significant gains in most cases. What would make MySQL Multiple Queries API more usable ? Allow to … as same set grouped by site (to show sites with most matches) as well to group by date to show history… similar case we have is with ClickAider For reporting needs we quite frequently need to have …
Post: Shard-Query turbo charges Infobright community edition (ICE)
May 6, 2011 By 22 Comments
… must register and report ontime information with the FAA, so there are only 29 air carriers in the table: mysql> select count…>10 GROUP BY Year) t JOIN (select Year, count(*) as c2 from ontime_fact join dim_date using (date_id) WHERE DepDelay>10 GROUP BY Year…_date using (date_id) WHERE DepDelay>10 GROUP BY Year) t JOIN (select Year, count(*) as c2 from ontime_fact join dim_date using (date_id) GROUP BY…
Post: Advanced index analysis with mk-index-usage
November 11, 2010 By 2 Comments
… report …wordpress.wp_posts.type_status_date | 2 | 15 | 30…mysql> SELECT CONCAT_WS(‘.’, db, tbl, idx) AS idx, -> GROUP_CONCAT(alt_idx) AS alternatives, -> GROUP_CONCAT(DISTINCT query_id) AS queries, SUM(cnt) AS cnt -> FROM index_alternatives -> GROUP BY…
Post: How Percona does a MySQL Performance Audit
November 24, 2008 By 17 Comments
… with these commands: date uname -a cat /… LVM devices, volume groups, and logical volumes. At… bug report (not a bug). Looking at MySQL status … go through it line by line, like this: +———————————–+———————-+———————-+ | Variable_name | Value | Value …
Comment: Full text search for all MySQL Storage Engines
June 29, 2006 By 37 Comments
… somewhat bad when you need a sort by date). The reported index size on disk is including mysqls own columndefinitions etc. What would be… 200 sorted by date. For mysql fulltext that means that if each of the 10 common words produces 150k hits internally the mysql server… the internal hits, group them, sort them by date and finally pick up the 200 best matches (desc by date or asc by date). As you can…
Post: Using flow control functions for performance monitoring queries
May 24, 2008 By 12 Comments
…hardly be optimized well by MySQL Optimizer. One way …for some site and reports number of requests, …_080523 where bot=’google’ group by page_type; +——-+——————-+——–+——–+——–+—————+ | cnt | avw | soso | poor | fatal |… rows to obfuscate some date 
In this query …
Post: New OLAP Wikistat benchmark: Introduction and call for feedbacks
January 28, 2010 By 16 Comments
…date_id ) JOIN projects p ON (p.id=project_id ) WHERE di.calmonth=7 and di.calyear=2009 GROUP BY project ORDER BY sm DESC LIMIT 20; I am going to load data and run queries against available engines: MySQL…
Post: How adding another table to JOIN can improve performance ?
August 1, 2008 By 12 Comments
… take a look at very typical reporting query which queries data for date range for multiple of groups (these can be devices, pages… avoid. It is easy to block equality propagation by using some trivial function: mysql> explain select sum(events) from info,dl where… some reason MySQL decides to only do “ref” on the date only instead of using range on day and list of groups for…
Post: Intro to OLAP
July 12, 2010 By 9 Comments
… online analytical processing) tools using MySQL and other free open source…groups. A typical OLAP analysis might show “sale total, by year, by sales rep, by product category”. OLAP analysis is usually used for reporting… are degenerate: gender, region, style, date. [caption id=”attachment_3264″ align…
