…row in set (0.06 sec) mysql> SELECT SQL_NO_CACHE name FROM people WHERE age BETWEEN 18 AND 22 AND zip IN…
Post: Flexviews - part 3 - improving query performance using materialized views
April 4, 2011 By 12 Comments
… important thing to know right now is that there are two significant differences between the methods. A view which uses the complete refresh…: 999 3 rows in set (0.00 sec) Using the dictionary The following SQL is dynamically generated by the flexviews.get_sql() function call… from dashboard_top_customers\G *************************** 1. row *************************** cnt: 155187034 1 row in set (0.03 sec) Notice the difference in response time. Refresh method performance comparison…
Post: A workaround for the performance problems of TEMPTABLE views
May 19, 2010 By 7 Comments
…in such a way that it can be shared between views. This example is somewhere in between those two…row in set (0.01 sec) Notice the vastly different plan over the old one. We no longer examine millions of rows… 3 rows in set (0.01 sec) In review, I like views for their convenient encapsulation of SQL…
Post: SHOW INNODB STATUS walk through
July 17, 2006 By 40 Comments
…spins 3463, OS waits 3163 There are two portions in this section. One is list …in use 1, locked 0 MySQL thread id 8079, query id 728899 localhost root Sending data select sql_calc_found_rows…In some rare cases purge also could have hard time to keep up with update rate, in this case difference between…
Post: Using Flexviews - part one, introduction to materialized views
March 23, 2011 By 2 Comments
…SQL query, and what we normally think of as a result set is really a virtual table. It has columns and rows… synced to the same point in time.  There are two different methods by which a … changed in the database Delta computation – How it uses those changes to compute the differences between the …
Post: Data mart or data warehouse?
July 15, 2010 By 13 Comments
…two in my six part series on business intelligence, with a focus on OLAP analysis. Part 1 – Intro to OLAP Identifying the differences between a data warehouse and a data mart. (this post) Introduction to MDX and the kind of SQL…
Post: Intro to OLAP
July 12, 2010 By 9 Comments
…differences between a data warehouse, and a data mart. Introduction to MDX queries and the kind of SQL…in many cases. In the example below that there is no actual dimension table which includes the two different…
Post: How SHOW SLAVE STATUS relates to CHANGE MASTER TO
July 7, 2008 By 21 Comments
…which also means there are two replication threads “IO Thread” and “SQL Thread” used in the process but …_Behind_Master: 0 1 row in set (0.00 sec) As you can see in this output there … difference between IO Thread position in Master logs (Master_Log_File:Read_Master_Log_Pos) and SQL Thread position in…
Post: Why MySQL could be slow with large tables ?
June 9, 2006 By 139 Comments
…management system is different in some respect and what works well for Oracle,MS SQL, PostgreSQL may …) from large where val between 1 and 100; +————+ | count(pad) | +————+ | 314008 | +————+ 1 row in set (29 … be close to two full table scans (as 60mil of rows need to be read) …
Post: mk-query-digest, query comments and the query cache
…in a specially formatted SQL comment. This information can then be cross-referenced between… second part includes two expressions, …in each iteration of the loop. The queries differ only by comments. In…in Percona Server at runtime: mysql> set global query_cache_strip_comments=’ON’; Query OK, 0 rows…