… compromise, either of performance, or of accuracy of randomness. The ORDER BY RAND() solution is known to have poor performance, but it… — it accesses these first, by looking up the keywords you specify. It doesn’t matter what order you specify the keywords in…, grouping by kind_id in index order. This made the first table in the EXPLAIN seem like it was scanning more rows. But by…
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 …52G innodb_log_file_size = 2000M innodb_log_files_in_group = 2 innodb_file_per_table = true innodb_read_…
Post: Galera Flow Control in Percona XtraDB Cluster for MySQL
May 2, 2013 By 5 Comments
… This prevents any node in the synchronous replication group from getting too far behind the others in…transactions are copied to all nodes and global ordering is established, but apply and commit is …applying on any node by simply by running “FLUSH TABLES WITH READ LOCK”, or perhaps by “LOCK TABLE”, …
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 …s law for Galera. We must have group communication to replicate and establish global ordering for every transaction, and the expense …
Post: Using GROUP BY WITH ROLLUP for Reporting Performance Optimization
September 17, 2007 By 14 Comments
… like “a%” group by grp with rollup order by cnt desc limit 11; ERROR 1221 (HY000): Incorrect usage of CUBE/ROLLUP and ORDER BY Oops. Bad… why GROUP BY WITH ROLLUP is slower and why ORDER BY does not work with it. The thing is – it is using filesort as group by execution method, not temporary table as ordinary GROUP BY…
Post: Air traffic queries in LucidDB
October 26, 2009 By 20 Comments
…” WHERE “DepDelay”>10 AND “Year” BETWEEN 2000 AND 2008 GROUP BY “DayOfWeek” ORDER BY c DESC; LucidDB: 27.131 seconds InfoBright: 6.37 sec… “OriginCityName”) FROM “ontime” WHERE “Year” BETWEEN 2008 and 2008 GROUP BY “DestCityName” ORDER BY 2 DESC; Years, LucidDB, InfoBright, MonetDB 1y, 6.76s, 1…
Post: How much overhead is caused by on disk temporary tables
August 16, 2007 By 26 Comments
…table. Results: mysql> select count(*) cnt,c from gtest group by c order by null limit 10; +—–+——————————————+ | cnt | c | +—–+——————————————+ | 2 | 80c9a87595687ccb33fa525d396ee75658aec777 | | 1 | 05bdcc2c1bb051cff861cee1ffcb7f680922b13e…
Post: Shard-Query adds parallelism to queries
November 15, 2010 By 18 Comments
… WHERE FlightDate BETWEEN ’2000-01-01′ AND ’2008-12-31′ GROUP BY DayOfWeek ORDER BY c DESC; Q3 – SELECT Origin, count(*) AS c FROM… AND FlightDate BETWEEN ’2000-01-01′ AND ’2008-12-31′ GROUP BY Origin ORDER BY c DESC LIMIT 10; Q4 – SELECT carrier, count(*) FROM… FlightDate between ’2007-01-01′ and ’2007-12-31′ GROUP BY carrier ORDER BY 2 DESC; .tblGenFixed td {padding:0 3px;overflow:hidden;white…
Post: 3 ways MySQL uses indexes
September 12, 2009 By 28 Comments
… at the same index (A,B) things like ORDER BY A ; ORDER BY A,B ; ORDER BY A DESC, B DESC will be able to use… work A=5 ORDER BY B ; A=5 ORDER BY B DESC; A>5 ORDER BY A ; A>5 ORDER BY A,B ; A>5 ORDER BY A DESC which… indexes. You can also see others like using index for group by but I think they can be pretty much looked as…
