… SQL can produce an infinite variety of queries. The query optimizer analyzes queries for common patterns the MySQL designers know can… amount of engineer-years spent developing and testing the query optimizer. The developers have to make a decision about which query… form. Q: Doesn’t the primary key solution for random selection only work when the IDs for movies are distributed uniformly…
Post: Webinar: SQL Query Patterns, Optimized
May 20, 2013 By Leave a Comment
… built-in methods to analyze them for optimal query efficiency. The discussion will cover optimizer reports, query profiling, and session status to measure performance. The query patterns will include: Exclusion Join Random Selection Greatest…
Post: More on MySQL transaction descriptors optimization
April 26, 2013 By Leave a Comment
… the perfect case for read-only transaction optimization in MySQL 5.6, because all SELECT queries in the AUTOCOMMIT mode are, by… a non-trivial amount of updates to the database, all SELECT queries, even those participating in read-only transactions, start suffering… 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
… MySQL transactions created by a non-locking SELECT statement in the autocommit mode. The relevant optimization in MySQL 5.6 boils down… POINT_SELECT benchmark, i.e. all queries are single-statement SELECT statements in autocommit. Which is the perfect case for read-only optimization… read-only optimization in MySQL 5.6 does not kick in. The default sysbench POINT_SELECT mode benchmark does PK lookup SELECT queries…
Post: 5 Percona Toolkit Tools for MySQL That Could Save Your Day: April 3 Webinar
March 22, 2013 By Leave a Comment
… tools: pt-query-digest, to select the queries you should try to improve to get optimal response times pt-archiver, to efficiently…-world MySQL database administration challenges, such as: Selecting which queries you should try to optimize to get better response times How to…
Post: Memory allocators: MySQL performance improvements in Percona Server 5.5.30-30.2
April 15, 2013 By
… heap area and then creates requested block(s) there. That optimization helps to avoid fragmentation in case of many small allocations… needs. To demonstrate the difference we have run sysbench POINT_SELECT test for glibc and jemalloc allocators. Observations: = MySQL 5.5… with transaction list scanning (due to the read-only transactions optimization), but it still allocates/frees memory for read view structure…
Post: What I'm looking forward to at Percona Live (MySQL Users Conference)
April 4, 2013 By 1 Comment
… will give an Innodb and Performance Optimization Tutorial as well as talk about Goal-Driven Performance Optimization and Choosing storage systems for… MySQL technology and services, then you’ll see the largest selection of MySQL Ecosystem vendors at the show. If you’re…
Post: MySQL 5.6.10 Optimizer Limitations: Index Condition Pushdown
March 14, 2013 By 2 Comments
… (although not very impacting) optimizer issue: a “SELECT *” taking half the time to execute than the same “SELECT one_indexed_column” query in… optimization: mysql> EXPLAIN SELECT role_id FROM cast_info WHERE role_id = 1 and note like ‘%Jaime%’\G *************************** 1. row *************************** id: 1 select…
Post: The Optimization That (Often) Isn't: Index Merge Intersection
December 14, 2012 By 6 Comments
… EXPLAIN produced the following execution plan: *************************** 1. row *************************** id: 1 select_type: SIMPLE table: users type: index_merge possible_keys: PRIMARY… very elegant and it’s a bit messy: SELECT user_id FROM (SELECT user_id,parent_id,status FROM users WHERE user…, altering the table structure, or creating messy sub-selects. In cases where the optimizer just can’t get things right, and you…
Post: ORDER BY ... LIMIT Performance Optimization
September 1, 2006 By 100 Comments
… so ORDER BY can be optimized even if it is done by second table: mysql> explain select test.i from test, test… able to use it as Optimizer is not smart enough yet to detect such cases: mysql> explain select test.i from test… if needed In some cases MySQL Optimizer may prefer to use different index, which has better selectivity or just better estimates instead…
