… s4 are also lagging behind). s2> select count(*) from t; +———-+ | count(*) | +———-+ | 2 | +———-+ # s1 is behind s1> select count(*) from t; +———-+ | count(*) | +———-+ | 0… fixed, it is good to be aware of the current limitations.
Post: More on MySQL transaction descriptors optimization
April 26, 2013 By Leave a Comment
… a non-trivial amount of updates to the database, all SELECT queries, even those participating in read-only transactions, start suffering… the mix, limitations of read-only transactions become obvious. In the following test sysbench does 9 primary key SELECTs followed by a… buffer pool we use following queries: select avg(id) from sbtest$i force key (primary) select count(*) from sbtest$i WHERE k…
Post: trx descriptors: MySQL performance improvements in Percona Server 5.5.30-30.2
April 12, 2013 By 3 Comments
… read-only limitations. Let’s take a look at the following benchmark: This is the so-called QPS-mode POINT_SELECT benchmark, i.e. all queries are single-statement SELECT statements in autocommit. Which is the… does not kick in. The default sysbench POINT_SELECT mode benchmark does PK lookup SELECT queries wrapped into regular (i.e. not…
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 !
Post: Memory allocators: MySQL performance improvements in Percona Server 5.5.30-30.2
April 15, 2013 By
…_SELECT test for glibc and jemalloc allocators. Observations: = MySQL 5.5.30 – throughput of MySQL 5.5.30 with glibc is limited…
Comment: Using index for ORDER BY vs restricting number of rows.
April 11, 2013 By 8 Comments
… follow。 I wrote: SELECT follow,count(*) AS NUM FROM wp_fans GROUP BY follow order by NUM desc limit 5 Follow was…
Post: ORDER BY ... LIMIT Performance Optimization
September 1, 2006 By 100 Comments
… with LIMIT in the back end. In general this type of ORDER BY looks like: SELECT ….. WHERE [conditions] ORDER BY [sort] LIMIT N… if it is quite fast: mysql> explain select * from test order by k limit 5; +—-+————-+——-+——-+—————+——+———+——+———+——-+ | id | select_type | table | type | possible_keys | key…
Post: Possible optimization for sort_merge and UNION ORDER BY LIMIT
September 18, 2007 By 10 Comments
… ord desc limit 10) union (select * from utest where c2=5 order by ord desc limit 10) order by ord desc limit 10 \G…> explain (select * from utest where c1=5 ) union (select * from utest where c2=5 ) limit 10 \G *************************** 1. row *************************** id: 1 select_type: PRIMARY…
Post: Ultimate MySQL variable and status reference list
January 8, 2011 By 4 Comments
… secure_file_privblogpercona.commanual Select_full_joinblogpercona.commanual Select_full_range_joinblogpercona.commanual Select_rangeblogpercona.commanual Select_range_checkblogpercona.commanual Select_scanblogpercona.commanual server_idblogpercona… sql_quote_show_createblogpercona.commanual sql_safe_updatesblogpercona.commanual sql_select_limitblogpercona.commanual sql_slave_skip_counterblogpercona.commanual sql_warningsblogpercona.commanual…
Post: Using delayed JOIN to optimize count(*) and LIMIT queries
April 6, 2007 By 22 Comments
… similar way MySQL generates full rows while executing queries with limit before throwing them away which makes queries with high offset… sec) mysql> select count(*) from fact where i select count(*) from fact left join dim on val=id where i select i,pad from fact left join dim on val=id where i select i,pad from ( select i,val…
