… pre-compute joins and may aggregate and summarize data. Using the aggregated data can significantly improve query response times compared to accessing… the `flexviews`.`mvlogs` metadata table, and it creates the changelog table itself. $ php add_table.php –schema=test –table=demo success Note that you… changes we made. You will notice that the source table only has two columns, but the changelog contains five. All change logs…
Post: Derived Tables and Views Performance
August 31, 2006 By 16 Comments
… related to each other but how do they compare in terms of performance ? Derived Tables in MySQL 5.0 seems to have… them in the temporary table, furthermore temporary table with no indexes (so you really do not want to join two derived tables for example). One more thing to watch for is the fact derived table is going to…
Post: MySQL 5.6 vs MySQL 5.5 and the Star Schema Benchmark
March 11, 2013 By 7 Comments
… here. Just a few notes: I tested two scenarios: a buffer pool much smaller than… was indeed the problem I decided to compare performance with a buffer pool much larger…joining the tables in the other direction mysql> select straight_join sum(lo_extendedprice*lo_discount) as revenue from dim_date join…
Post: Flexviews - part 3 - improving query performance using materialized views
April 4, 2011 By 20 Comments
…table_name: orders mview_table_schema: demo mview_table_alias: o mview_join_condition: NULL mview_join_order: 999 *************************** 2. row *************************** mview_table…Refreshed both views. Compared the refresh …68 sec) Conclusion Flexviews supports two refresh methods, …
Post: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5
March 21, 2012 By 10 Comments
… following query executed on the InnoDB table: SELECT non_key_column FROM tbl…if the buffer size is small as compared to the combined size of the…when MRR is used then there are two separate calls made to the storage… this works only with joins and specifically with Block Access Join Algorithms. So I…
Post: Intro to OLAP
July 12, 2010 By 10 Comments
… using aggregation. Using materialized views to automate that aggregation process. Comparing the performance of OLAP with and without aggregation over multiple… is no actual dimension table which includes the two different order statuses. Such a dimension would add an extra join, which is expensive…
Post: Shard-Query EC2 images available
May 11, 2011 By 2 Comments
… ICE gets over 16:1 compression ratio(compared to Innodb, 8:1 compared to raw input data), which is … should run the workers on one or two nodes. You should start two workers per core in the cluster. …sleep-delay=0 innodb-use-sys-stats-table innodb-write-io-threads=4 join-buffer-size=16M key-buffer-…
Post: Using Flexviews - part one, introduction to materialized views
March 23, 2011 By 3 Comments
… in a table. Storing the results of a SQL statement into a table (even a temporary table) is called …the data is already joined together and pre-aggregated, CPU and memory usage may be reduced compared to computing … same point in time.  There are two different methods by which a MV can be …
Post: New OLAP Wikistat benchmark: Introduction and call for feedbacks
January 28, 2010 By 16 Comments
…I have next goals with proposed benchmark: Compare engines in OLAP queries for planning, …two months of stats we have about 172GB of data with about 7 billion rows in fact table. Example of query ( again, full list on Benchmark Wiki) SELECT project, sum(page_count) sm FROM pagestat JOIN…
Comment: Why MySQL could be slow with large tables ?
December 4, 2008 By 141 Comments
… “Bigland” was just 75K rows compared to rows with “ServiceA” which was 50K rows. The initial table (unit) was 100K rows. Each row record is approx. 500B. So adding ONE JOIN extra, with an additional 75K rows to JOIN… SQL, since it seems like MySQL handles ONE JOIN, but no way it handles TWO JOINS. Sincerly grateful for any input here! Morten…
