…, COUNT(1) as installed_count from client_app_inventory cai inner join device d on (cai.client_id = d.client_id) inner join label_to_device ld on (ld.device_id = d.id) where ld.trash = ‘f’ and cai.trash = ‘f’ and (cai…
Comment: Derived Tables and Views Performance
May 21, 2013 By 16 Comments
…/2013/05/simple-mysql-and-php-prodcuts-and-cart.html create dynamic main menu and sub menu using php and mysql Count FROM `menu` a LEFT OUTER JOIN (SELECT parent, COUNT(*) AS Count FROM `menu` GROUP BY parent) Deriv1…['label'] . “”; display_menu($row['id'], $level + 1); echo “”; } elseif ($row['Count']==0) { echo “” . $row['label'] . “”; } else; } echo “”; } display_menu(0, 1…
Post: MySQL Query Patterns, Optimized - Webinar questions followup
June 3, 2013 By 2 Comments
… you thought about doing that and why it helped? When I did not use the STRAIGHT_JOIN, the query optimizer reordered the… the result was that it created a temporary table to count the movies per production year for each kind_id. It…: Check out upcoming Percona Training classes in North America and Europe. Join Percona and the MySQL community at our events. The next one…
Post: Is Synchronous Replication right for your app?
May 14, 2013 By 18 Comments
… redis and only flushing to the database periodically? if( $last_count % 100 == 0 ) { $db->do( “UPDATE achievements SET count = $last_count where achievement = ‘killed_troll’”; } change your schema In Example 2, above, how above moving the ‘joined‘ column …
Post: Virident vCache vs. FlashCache: Part 2
May 17, 2013 By 3 Comments
… configuration C avoids the spikes and produces a very constant and predictable result. vCache vs. FlashCache -… –test=tests/db/oltp.lua –oltp_tables_count=32 \ –oltp-table-size=10000000 –rand-init=…heap_table_size = 64M sort_buffer_size = 4M join_buffer_size = 4M thread_cache_size = 1000…
Post: Benchmarking Percona Server TokuDB vs InnoDB
May 7, 2013 By 32 Comments
… DEFAULT ’0′, PRIMARY KEY (`hid`,`mid`,`id`) ) and transactions are: $HID=rand(0,10000) // distributed…_log = 50 max_connections = 2000 max_prepared_stmt_count=500000 max_connect_errors = 10 table_open_cache…_heap_table_size = 64M sort_buffer_size = 4M join_buffer_size = 4M thread_cache_size = 1000…
Post: Using delayed JOIN to optimize count(*) and LIMIT queries
April 6, 2007 By 22 Comments
… and dimension tables which contain more information about facts and which need to be joined to get query result. If you’re executing count…. To get better performance you can “Help” MySQL and remove JOIN for count(*) and do JOIN after limiting result set for retrieval queries. Lets look… count(*) from fact; +———-+ | count(*) | +———-+ | 7340032 | +———-+ 1 row in set (0.00 sec) mysql> select count(*) from fact where i select count(*) from fact left join…
Post: MySQL caching methods and tips
April 4, 2011 By 5 Comments
… is expensive, and even if the disk bottleneck is eliminated, the sorting, aggregation and join operations are still CPU intensive and single threaded. In… example and then the duplicate items are deleted. When this happens, decreasing the counts in summary tables may not be possible and thus…
Post: MySQL 5.6 vs MySQL 5.5 and the Star Schema Benchmark
March 11, 2013 By 7 Comments
…_pool from ( select table_name, index_name, count(*) pages, sum(is_old=’YES’) old, count(*) – sum(is_old=’YES’) hot, sum(number… join dim_date on lo_orderdatekey = d_datekey where d_yearmonth = 199401 and lo_discount between 4 and 6 and lo_quantity between 26 and… lineorder join dim_date on lo_orderdatekey = d_datekey where d_weeknuminyear = 6 and d_year = 1994 and lo_discount between 5 and 7 and…
Post: Flexviews - part 3 - improving query performance using materialized views
April 4, 2011 By 20 Comments
…table, three orders of magnitude more quickly than COUNT(*). mysql> select count(*) cnt from order_lines\G *************************** 1. row *************************** cnt: …at only what changed, even for MVs with aggregation and joins, is the value proposition for Flexviews. The flexviews.refresh…
