…MySQL Query Patterns, Optimized” for Percona MySQL Webinars. If you missed it, you can still register to view…of subqueries/multiple joins vs. multiple queries (e.g. temp tables)? For performance… in a different order from what you specify in … result was that it created a temporary table to count the…
Post: Flexviews - part 3 - improving query performance using materialized views
April 4, 2011 By 20 Comments
…table changelogs which are created by FlexCDC. The refresh algorithm computes changes in multiple… create incrementally refreshable views from SQL statements. It reads one or more “CREATE TABLE …tables used by the above view, as stored in the data dictionary: mysql> select * from flexviews.mview_table…
Post: Quickly finding unused indexes (and estimating their size)
December 5, 2012 By 2 Comments
…tables: mysql> create view used_indexes as (select TABLE_SCHEMA, TABLE_NAME, INDEX_NAME from master_index_stats ) UNION DISTINCT (select TABLE_SCHEMA, TABLE_NAME, INDEX_NAME from…
Post: The Doom of Multiple Storage Engines
May 8, 2010 By 22 Comments
…”Selling Points” of MySQL is support for Multiple Storage engines, and from the glance view it is indeed…data from memory when it fits there. Optimizer and Execution Storage engines are not created …combination such as running multi-table update joining PBXT and Innodb tables. Operations Things like …
Post: Using Flexviews - part one, introduction to materialized views
March 23, 2011 By 3 Comments
…SQL statement. MySQL even includes a SQL statement that makes this perfectly clear: CREATE TABLE .. AS SELECT …result. It is not possible to create multiple different tables via CTAS and have them all be…the view from scratch. This is the less desirable method: During a complete refresh, the view …
Post: Data mart or data warehouse?
July 15, 2010 By 14 Comments
… it is very hard to create a dimensional model from a highly normalized database schema…. from the warehouse data. A different approach is to build a relational warehouse from multiple …tables and materialized views. These features make working with a star schema much easier than it may be on MySQL…
Post: Using VIEW to reduce number of tables used
September 28, 2007 By 12 Comments
…table header modification, which is costly. Of course if you can simply rewrite software to store multiple users per table…table which has user_id,id, title,body columns and create post123 as view: create view post123 as select id,title,body from…
Post: Percona XtraDB Cluster: Multi-node writing and Unexpected deadlocks
August 17, 2012 By 17 Comments
…create a table to test on and insert some data: node2 mysql> create table…view will not see trx with id >= 83C, sees < 83C TABLE LOCK table…mysql> commit; Query OK, 0 rows affected (0.00 sec) node3 mysql> select * from autoinc; … Really whenever transactions across multiple nodes can take the…
Post: Differences between READ-COMMITTED and REPEATABLE-READ transaction isolation levels
August 28, 2012 By 3 Comments
…MySQL…multiple version concurrency control) and it is accomplished using row versioning and UNDO information. In REPEATABLE READ InnoDB also creates gap locks for range scans. select * from some_table where id > 100 FOR UPDATE; The above update will create…
Post: Distributed Set Processing with Shard-Query
May 14, 2011 By 27 Comments
…result set” is a SET created by the output of…table from each node correspond logically to a records in a Flexviews materialized view delta table… work, due to the multiple levels of result set …MySQL storage nodes are supported. Amdahl’s law applies to the distributed processing. The results from…
