… time the view is refreshed. This is similar to storing the results of a query in Memcache. When the cache “expires”, the contents must… the list of tables used by the above view, as stored in the data dictionary: mysql> select * from flexviews.mview_table where mview_id… any particular customer. Notice how you can use this summary to calculate the number of lines in the order_lines table, three orders of…
Post: Flexviews - part 3 - improving query performance using materialized views
April 4, 2011 By 20 Comments
Post: 10+ Ways to Crash or Overload MySQL
November 13, 2007 By 20 Comments
…to run their queries. Stored Procedures – How much memory can stored procedure allocate ? say can you create 1000 variables in stored procedure and set 1M result set to…
Post: Can MySQL temporary tables be made safe for statement-based replication?
May 26, 2008 By 8 Comments
…how to make MySQL replication reliable, part of which is to eliminate temporary tables…Error: Error ‘Table ‘test.t’ doesn’t exist’ on query. Default database: ”. Query: ‘…table test.t; -> commit; -> end// master > delimiter; Now calling the stored procedure should put a row into the test.ins table…
Post: Intro to OLAP
July 12, 2010 By 10 Comments
…to store multidimensional data for OLAP analysis OLAP servers typically come in two basic flavors. Some servers have specialized data stores which store data in…to write queries or drag and drop a report together in order to…
Post: Using Flexviews - part one, introduction to materialized views
March 23, 2011 By 3 Comments
… similar to a regular view, in that it represents the result set of a query, but the contents are stored (materialized!) as a real table… Delta application – How Flexviews uses the deltas to actually make the changes Finally, the last blog post will describe how to use Flexviews for…
Post: Recovering Innodb table Corruption
July 4, 2008 By 22 Comments
… as MySQL crashes it does not store all data it could recover to MyISAM table. Using series of queries with LIMIT can be handly… crashes to make sure indexes are not corrupted. So we looked at how to get your data back from simple Innodb Table Corruption. In more complex cases you may need to use higher innodb_force_recovery modes to…
Post: MySQL Query Cache
July 27, 2006 By 70 Comments
…Queries are constantly being invalidated from query cache by table updates, this means number of queries in…query cache option query_cache_limit was implemented which limits result set which can be stored in query cache. If you need larger queries to…
Post: Moving Subtrees in Closure Table Hierarchies
February 14, 2011 By 18 Comments
…to store hierarchical data, such as threaded comments, personnel org charts, or nested bill-of-materials. Sometimes it’s tricky to do this in… Closure Table design, I periodically get questions about how to solve …table in a single query in MySQL. But we can use MySQL’s multi-table DELETE syntax, to…
Post: Statement based replication with Stored Functions, Triggers and Events
December 16, 2011 By 4 Comments
… needs to log “not usual” queries like Events, Functions, Stored Procedures, Local Variables, etc. We’ll learn what problems can we have and how to… our stored procedure write data to our tables the queries inside the procedure get logged and not the call to the procedure itself. So in this…
Post: Data compression in InnoDB for text and blob fields
May 30, 2012 By 2 Comments
…queries exceeding 500MB (!!), and stored in a 100GB table. In this case we were not allowed to make any query or application logic changes so we chose to…
