… compromise, 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… MySQL to scan the `title` table first, grouping by kind_id in index order. This made the first table in the EXPLAIN…
Comment: Using index for ORDER BY vs restricting number of rows.
April 11, 2013 By 8 Comments
… number 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
…ordering really, really matters (really!). By enforcing replication to all nodes, we can (simultaneously) establish global ordering for the transaction, so by… periodically? if( $last_count % 100 == 0 ) { $db->do( “UPDATE achievements SET count = $last_count where achievement = ‘killed_troll’”; …
Post: More on MySQL transaction descriptors optimization
April 26, 2013 By Leave a Comment
… MySQL 5.5 and MySQL 5.6. In order to understand more about differences between results we …, start suffering from the trx_list overhead created by concurrent updates. Once we step away from this …–num-threads=<1..1024> –test=oltp.lua –oltp_tables_count=8 –oltp-table-size=1000000 –rand-init=on –…
Post: Is your MySQL buffer pool warm? Make it sweat!
April 16, 2013 By 6 Comments
… order to log every query. (See part two for handling massive slow log volume.) The slow logs are served, via HTTP, by… –query-log-stdin –dispatcher-plugin thread-pool –thread-pool-threads-count 100 –session-init-query \”set innodb_fake_changes=1\” > /var… on the standby server. You will need Percona Server in order to use innodb_fake_changes. Thread pool Percona Playback added…
Post: Using index for ORDER BY vs restricting number of rows.
February 16, 2007 By 8 Comments
… * from goods where cat_id=5 and seller_id=1 order by price desc limit 10 \G *************************** 1. row *************************** id: 1 select…(cat_id) where cat_id=5 and seller_id=1 order by price desc limit 10 \G *************************** 1. row *************************** id: 1 select… hint. The other problem you may have however is calculating count of matching rows which may be even trickier to slow…
Post: Using GROUP BY WITH ROLLUP for Reporting Performance Optimization
September 17, 2007 By 14 Comments
… the second way. mysql> select grp, count(*) cnt from dt where slack like “a%” group by grp order by cnt desc; +——-+—–+ | grp | cnt | +——-+—–+ | 9879… is relatively small: mysql> select grp, count(*) cnt from dt where slack like “a%” group by grp order by null; +——-+—–+ | grp | cnt | +——-+—–+ | 2257 | 251…
Post: How much overhead is caused by on disk temporary tables
August 16, 2007 By 26 Comments
… this. So what was performance mysql> select count(*) cnt,c from gt group by c order by null limit 10; +—–+——————————————+ | cnt | c | +—–+——————————————+ | 1 | 80c9a87595687ccb33fa525d396ee75658aec777… to keep temporary table. Results: mysql> select count(*) cnt,c from gtest group by c order by null limit 10; +—–+——————————————+ | cnt | c | +—–+——————————————+ | 2 | 80c9a87595687ccb33fa525d396ee75658aec777…
Post: Performance Schema tables stats
May 2, 2011 By 2 Comments
… mysql> select OBJECT_NAME,COUNT_STAR from table_io_waits_summary_by_table order by COUNT_STAR DESC LIMIT 5; +————-+————+ | OBJECT_NAME | COUNT_STAR | +————-+————+ | sbtest97 | 1854084… tables that required read IO mysql> select * from file_summary_by_instance order by COUNT_READ desc limit 6G *************************** 1. row *************************** FILE_NAME: /data…
Post: A rule of thumb for choosing column order in indexes
June 5, 2009 By 15 Comments
I wanted to share a little rule of thumb I sometimes use to decide which columns should come first in an index. This is not specific to MySQL, it’s generally applicable to any database server with b-tree indexes. And there are a bunch of subtleties, but I will also ignore those for the sake of …
