… have chosen a way of using Multiple Tables to solve Table Locks problem you can also use multiple Key Caches to reduce or virtually… table to single key cache – it would be so much more helpful if you could use multiple key caches for the same table…. mysql> select t.table_name,index_length/(select sum(index_length) from information_schema.tables where table_schema=’test’ and t.table_name in (‘a…
Post: Researching your MySQL table sizes
March 17, 2008 By 28 Comments
… by particular table “type” in sharded environment when multiple tables with same structure and similar name exists: SELECT count(*) tables, concat(round(sum(table_rows)/1000000,2),’M') rows, concat(round(sum(data…
Post: Multiple column index vs multiple indexes
August 22, 2008 By 13 Comments
… i=2 AND j BETWEEN 1 and 2 Q3 SELECT sum(length(val)) FROM T WHERE j=2 AND i BETWEEN… the table using index. Also Full Table Scan is preferred for Q4 in all cases but in case of high cardinality multiple index configuration. Note4 Q5 was just run on high cardinality tables to show what difference large…
Post: How adding another table to JOIN can improve performance ?
August 1, 2008 By 12 Comments
… data for date range for multiple of groups (these can be devices, pages, users …. etc) CREATE TABLE `info` ( `id` int(10) unsigned… (10,20,30,40,50,60,70,80,90,100); +————-+ | sum(events) | +————-+ | 3289092 | +————-+ 1 row in set (1.04 sec) mysql… performance by joining data to yet another table though why it does not work for multiple group remains question to check with…
Post: Hidden columns of query_review_history table
August 28, 2012 By Leave a Comment
…values for review trend analysis into query_review_history table. According to its official documentation you can populate many…of these MAGIC_history_cols values: pct|avt|cnt|sum|min|max|pct_95|stddev|median|rank If the…filtering queries in the database aggregating slow queries from multiple servers….
Post: Distributed Set Processing with Shard-Query
May 14, 2011 By 27 Comments
… databases do 99.9% of the work, due to the multiple levels of result set reduction. Finally, Shard-Query can automatically… `sum(DepDelay)`, SUM(`sum(DepDelay >= 0) flight_delayed`) AS `sum(DepDelay >= 0) flight_delayed` FROM `aggregation_tmp_74082863` GROUP BY 1 –POST EXEC: DROP TABLE…
Post: Flexviews - part 3 - improving query performance using materialized views
April 4, 2011 By 20 Comments
… refresh method uses table changelogs which are created by FlexCDC. The refresh algorithm computes changes in multiple transactions. After each …(‘demo’,'dashboard_top_customers’), -> ‘select customer_id, ‘> sum(total_price) total_price, ‘> sum(total_lines) total_lines ‘> from demo.dashboard_…
Post: MySQL 5.6 vs MySQL 5.5 and the Star Schema Benchmark
March 11, 2013 By 7 Comments
…_blocks_time=1000 on MySQL 5.5.30 and testing multiple buffer pools on MySQL 5.5.30. Finally, MySQL 5…_pool from ( select table_name, index_name, count(*) pages, sum(is_old=’YES’) old, count(*) – sum(is_old=’YES’) hot, sum(number_records) records…_pool from ( select table_name, index_name, count(*) pages, sum(is_old=’YES’) old, count(*) – sum(is_old=’YES’) hot, sum(number_records) records…
Post: Intro to OLAP
July 12, 2010 By 10 Comments
…, and it would usually be aggregated with SUM. The other information about the sale, such… in dimension tables. The fact table contains columns which are joined to the dimension tables, such as… year, month and quarter. You can create multiple different hierarchies from a single dimension. This allows…
Post: Using flow control functions for performance monitoring queries
May 24, 2008 By 12 Comments
…site performance analyzes. As you can see in this table we have recorded “wtime” which is wallclock time … count(*) cnt, avg(wtime) avw, sum(if(wtime>0.3,1,0))/count(*) soso, sum(if(wtime>1,1,… average request time as well as classifies requests to multiple classes. From the same query we can see …
