…, either of performance, or of accuracy of randomness. The ORDER BY RAND() solution is known to have poor performance, but it… rows in `title` by a secondary index. But the result was that it created a temporary table to count the movies per… case to force MySQL to scan the `title` table first, grouping by kind_id in index order. This made the first table…
Comment: INSERT INTO ... SELECT Performance with Innodb tables.
May 22, 2013 By 22 Comments
… as SELECT ld.label_id, cai.inventory_id, COUNT(1) as installed_count from client_app_inventory cai inner join device d… = ‘f’ and cai.trash = ‘f’ and (cai.status is NULL) group by ld.label_id, cai.inventory_id All 4 tables in…
Comment: Derived Tables and Views Performance
May 21, 2013 By 16 Comments
….label, a.link, Deriv1.Count FROM `menu` a LEFT OUTER JOIN (SELECT parent, COUNT(*) AS Count FROM `menu` GROUP BY parent) Deriv1 ON a.id = Deriv1.parent WHERE a.parent=” . $parent); echo “”; while ($row = mysql_fetch_assoc($result)) { if ($row['Count... + 1); echo ""; } elseif ($row['Count']==0) { echo “” . $row['label'] . “”; } else; } echo “”; } display_menu(0, 1); ?> Posted by bikash ranajan nayak at 10…
Comment: Using index for ORDER BY vs restricting number of rows.
April 11, 2013 By 8 Comments
… of follow and order by follow。 I wrote: SELECT follow,count(*) AS NUM FROM wp_fans GROUP BY follow order by NUM desc limit 5…
Post: Is Synchronous Replication right for your app?
May 14, 2013 By 18 Comments
…? Well, it turns out transaction ordering really, really matters (really!). By enforcing replication to all nodes, we can (simultaneously) establish global…_count where achievement = ‘killed_troll’”; } change your schema In Example 2, above, how above moving the ‘joined’ column to the users_groups…
Post: More on MySQL transaction descriptors optimization
April 26, 2013 By Leave a Comment
… start suffering from the trx_list overhead created by concurrent updates. Once we step away from this…innodb_log_file_size = 2000M innodb_log_files_in_group = 2 innodb_file_per_table = true innodb_read…-threads=<1..1024> –test=oltp.lua –oltp_tables_count=8 –oltp-table-size=1000000 –rand-init=on …
Post: Benchmarking Percona Server TokuDB vs InnoDB
May 7, 2013 By 32 Comments
…monotonically increasing ID $K=rand(0,10000) // distributed by pareto distribution $C, $PAD = random_string() …log_file_size = 4G innodb_log_files_in_group = 2 innodb_log_block_size=4096 #####plugin…log = 50 max_connections = 2000 max_prepared_stmt_count=500000 max_connect_errors = 10 table_open_cache…
Post: Virident vCache vs. FlashCache: Part 2
May 17, 2013 By 3 Comments
… testing conducted for this post were sponsored by Virident. First, some background information. All tests… –test=tests/db/oltp.lua –oltp_tables_count=32 \ –oltp-table-size=10000000 –rand-init=…log_file_size = 1G innodb_log_files_in_group = 2 innodb_purge_threads = 1 innodb_fast_shutdown …
Post: Using GROUP BY WITH ROLLUP for Reporting Performance Optimization
September 17, 2007 By 14 Comments
… as number of groups is relatively small: mysql> select grp, count(*) cnt from dt where slack like “a%” group by grp order by null; +——-+—–+ | grp… value for the groups: mysql> select grp, count(*) cnt from dt where slack like “a%” group by grp with rollup order by cnt desc limit… as group by execution method, not temporary table as ordinary GROUP BY: mysql> explain select grp, count(*) cnt from dt where slack like “a%” group by grp…
Post: Speeding up GROUP BY if you want aproximate results
March 7, 2008 By 24 Comments
Doing performance analyzes today I wanted to count how many hits come to the pages which get more … simple query: select sum(cnt) from (select count(*) cnt from performance_log_080306 group by page having cnt>2) pv; Unfortunately this query… group by a hash of the page instead of page itself: mysql> select sum(cnt) from (select count(*) cnt from performance_log_080306 group by…
