….label, a.link, Deriv1.Count FROM `menu` a LEFT OUTER JOIN (SELECT parent, COUNT(*) AS Count FROM `menu` GROUP BY parent) Deriv1 ON a…['label'] . “”; display_menu($row['id'], $level + 1); echo “”; } elseif ($row['Count']==0) { echo “” . $row['label'] . “”; } else; } echo “”; } display_menu(0, 1…
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…
Post: MySQL Query Patterns, Optimized - Webinar questions followup
June 3, 2013 By 2 Comments
… the result was that it created a temporary table to count the movies per production year for each kind_id. It… case to force MySQL to scan the `title` table first, grouping by kind_id in index order. This made the first…
Post: Is Synchronous Replication right for your app?
May 14, 2013 By 18 Comments
… to users_groups, but also updates groups with some metadata: BEGIN; INSERT INTO users_groups (user_id, group_id) VALUES (100, 1); UPDATE groups SET… 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 to the users_groups…
Post: More on MySQL transaction descriptors optimization
April 26, 2013 By Leave a Comment
… = 52G innodb_log_file_size = 2000M innodb_log_files_in_group = 2 innodb_file_per_table = true innodb_read_io_threads… sysbench –num-threads=<1..1024> –test=oltp.lua –oltp_tables_count=8 –oltp-table-size=1000000 –rand-init=on –report-interval… sysbench –num-threads=<1..1024> –test=oltp.lua –oltp_tables_count=8 –oltp-table-size=1000000 –rand-init=on –report-interval…
Post: Virident vCache vs. FlashCache: Part 2
May 17, 2013 By 3 Comments
… –num-threads=32 –test=tests/db/oltp.lua –oltp_tables_count=32 \ –oltp-table-size=10000000 –rand-init=on –report-interval… = 1 innodb_log_file_size = 1G innodb_log_files_in_group = 2 innodb_purge_threads = 1 innodb_fast_shutdown = 1 #not… = 50 wait_timeout = 120 max_connections = 5000 max_prepared_stmt_count=500000 max_connect_errors = 10 table_open_cache = 10240 max…
Post: Benchmarking Percona Server TokuDB vs InnoDB
May 7, 2013 By 32 Comments
….lua –oltp-table-size=10000 –mysql-user=root –oltp-tables-count=32 –mysql_table_engine=tokudb –oltp_auto_inc=on –max… = 36G innodb_log_file_size = 4G innodb_log_files_in_group = 2 innodb_log_block_size=4096 #####plugin options innodb_read… = 3306 back_log = 50 max_connections = 2000 max_prepared_stmt_count=500000 max_connect_errors = 10 table_open_cache = 2048 max…
Comment: Using index for ORDER BY vs restricting number of rows.
April 11, 2013 By 8 Comments
… order by follow。 I wrote: SELECT follow,count(*) AS NUM FROM wp_fans GROUP BY follow order by NUM desc limit 5…
Post: Using GROUP BY WITH ROLLUP for Reporting Performance Optimization
September 17, 2007 By 14 Comments
… using GROUP BY WITH ROLLUP so I can get grouped result set together with total value for the groups: mysql> select grp, count(*) cnt from dt where slack like “a%” group by… group by execution method, not temporary table as ordinary GROUP BY: mysql> explain select grp, count(*) cnt from dt where slack like “a%” group…
Comment: Implementing efficient counters with MySQL
July 2, 2007 By 16 Comments
… the count, grouping the hits from the last five minutes by key to reduce number of updates) Another optimization is to group the update queries by their counts. For example, if you have 100 rows… be increased by 3 you could group these into one query like this: UPDATE table SET count = count + 3 WHERE id IN(…); This…
