… a view which uses no aggregation, then MySQL will let us use the MERGE algorithm. Notice that there are no warnings: mysql> create algorithm=merge view v1… sec) Now, lets try to use the MERGE algorithm with an aggregation query: mysql> create algorithm=MERGE view v2 as select c1, count(*) from…
Post: Quickly finding unused indexes (and estimating their size)
December 5, 2012 By 2 Comments
… separate tables. Then I simply created a view of a UNION DISTINCT of those two tables: mysql> create view used_indexes as (select TABLE_SCHEMA, TABLE… need to update our droppable_indexes view to use that information: mysql> drop view if exists droppable_indexes; mysql> create view droppable_indexes as select all_indexes…
Post: MySQL VIEW as performance troublemaker
August 12, 2007 By 57 Comments
… see applications being built utilizing VIEWs functionality which appeared in MySQL 5.0 and quite frequently VIEWs are used to help in writing the… we solve the same problem having things more modular and using MySQL VIEWs ? mysql> create view user_counts as select user_id,count(*) cnt from comments…) So now lets create a very artificial query which will JOIN 2 views just to see how indexes are used: mysql> explain select uc…
Post: Using VIEW to reduce number of tables used
September 28, 2007 By 12 Comments
… to the views we can have “post” table which has user_id,id, title,body columns and create post123 as view: create view post123 as… of the views. You could think MySQL would look at the WHERE clause to figure out which value should be used but unfortunately… is not part of the view so would not be available even if MySQL would support triggers on VIEWs (which it does not…
Post: Using Flexviews - part two, change data capture
March 25, 2011 By 26 Comments
… a materialized view(MV)? It explained that an MV can pre-compute joins and may aggregate and summarize data. Using the aggregated… maintain materialized views. Most database servers have some spare CPU for binary log parsing. Run the setup script: This will create the…. Lets create a table, insert some rows, add a change log, delete rows and then insert some more of them: mysql> create table…
Post: Derived Tables and Views Performance
August 31, 2006 By 16 Comments
… sec) Query using view is fast again: mysql> create view v as select * from test; Query OK, 0 rows affected (0.08 sec) mysql> select * from… case. Consider using temporary views instead of derived tables If you really need to use subselect in from clause consider creating view using it in the…
Post: Data mart or data warehouse?
July 15, 2010 By 14 Comments
… using aggregation. Using materialized views to automate that aggregation process. Comparing the performance of OLAP with and without aggregation over multiple MySQL … of data. Highly normalized schema are created and maintained by ETL jobs. Creating and maintaining these jobs is often one …
Post: Percona XtraDB Cluster: Multi-node writing and Unexpected deadlocks
August 17, 2012 By 17 Comments
…mysql> show create table autoinc\G *************************** 1. row *************************** Table: autoinc Create Table: CREATE…MySQL thread id 3972, OS thread handle 0x7fddb84e0700, query id 16408 localhost root sleeping show engine innodb status Trx read view…
Post: How much memory can MySQL use in the worst case?
February 12, 2009 By 17 Comments
… memory usage?” Various formulas are in wide use, but none of them is fully correct. …and UNION may/will also create temp tables; the same applies to views and probably some other …* 100); Query OK, 0 rows affected (2.34 sec) mysql> select get_lock(@a, 1); +—————–+ | get_lock(@a, 1) | …
Post: Logging MySQL queries from the client instead of the server
February 2, 2011 By 2 Comments
… valuable way to examine query execution on your MySQL server. Queries are logged with timing information, and …it’s valuable to be able to see both views of query execution time. Why would you want …. For example, if you are using PHP with mysqlnd, you can create a plugin that logs the queries, …
