… form. Q: Doesn’t the primary key solution for random selection only work when the IDs for movies are distributed uniformly… compromise, either of performance, or of accuracy of randomness. The ORDER BY RAND() solution is known to have poor performance, but it… MySQL to scan the `title` table first, grouping by kind_id in index order. This made the first table in the EXPLAIN…
Comment: ORDER BY ... LIMIT Performance Optimization
April 5, 2013 By 100 Comments
Hello everybody, We tried the solution base on query see in older post : SELECT * FROM sites WHERE category_id=5 ORDER BY date_created DESC LIMIT 10 changed to select * FROM (SELECT * FROM sites WHERE category_id=5 ORDER BY date_created DESC) as my_table LIMIT 10 It works for us !
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: More on MySQL transaction descriptors optimization
April 26, 2013 By Leave a Comment
…. In the following test sysbench does 9 primary key SELECTs followed by a PK UPDATE statement in each thread in a loop…=/tmp/mysql.sock –oltp-point-selects=1 –oltp-simple-ranges=0 –oltp-sum-ranges=0 –oltp-order-ranges=0 –oltp-distinct-ranges…=/tmp/mysql.sock –oltp-point-selects=9 –oltp-simple-ranges=0 –oltp-sum-ranges=0 –oltp-order-ranges=0 –oltp-distinct-ranges…
Post: trx descriptors: MySQL performance improvements in Percona Server 5.5.30-30.2
April 12, 2013 By 3 Comments
…, depending on many factors, can be from few times to orders of magnitudes faster than a list scan. You may be… started with START TRANSACTION READ ONLY MySQL transactions created by a non-locking SELECT statement in the autocommit mode. The relevant optimization… does not kick in. The default sysbench POINT_SELECT mode benchmark does PK lookup SELECT queries wrapped into regular (i.e. not…
Post: ORDER BY ... LIMIT Performance Optimization
September 1, 2006 By 100 Comments
… would often require ORDER BY with LIMIT in the back end. In general this type of ORDER BY looks like: SELECT ….. WHERE [conditions] ORDER BY [sort] LIMIT N,M Make sure it uses index It is very important to have ORDER BY…
Post: Possible optimization for sort_merge and UNION ORDER BY LIMIT
September 18, 2007 By 10 Comments
… (select * from utest where c1=5 order by ord desc limit 10) union (select * from utest where c2=5 order by ord desc limit 10) order by ord desc limit 10 \G *************************** 1. row *************************** id: 1 select_type…
Post: MySQL Performance - eliminating ORDER BY function
October 17, 2007 By 6 Comments
…> explain select * from tst where i=5 and date(d)=date(now()) order by date(d) \G *************************** 1. row *************************** id: 1 select_type: SIMPLE…) mysql> explain select * from tst where i=5 and date(d)=date(now()) order by d \G *************************** 1. row *************************** id: 1 select_type: SIMPLE…
Post: Using index for ORDER BY vs restricting number of rows.
February 16, 2007 By 8 Comments
… select * from goods where cat_id=5 and seller_id=1 order by price desc limit 10 \G *************************** 1. row *************************** id: 1 select_type…_id=5 and seller_id=1 order by price desc limit 10 \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: goods type…
Post: Using GROUP BY WITH ROLLUP for Reporting Performance Optimization
September 17, 2007 By 14 Comments
… groups is relatively small: mysql> select grp, count(*) cnt from dt where slack like “a%” group by grp order by null; +——-+—–+ | grp | cnt | +——-+—–+ | 2257… Three: mysql> select * from (select grp, count(*) cnt from dt where slack like “a%” group by grp with rollup) t order by cnt desc limit…
