…used in results set which clearly puts query as optimization candidate. It is not always possible to optimize queries…optimize such queries. I’m just describing the possibility. Sounds nice as described…
Post: How adding another table to JOIN can improve performance ?
August 1, 2008 By 12 Comments
…m speaking about comes from the issue with MySQL optimizer stopping using further index key parts as soon as…is not possible. I have described couple of solutions to this problem – using IN list instead of … query with BETWEEN range replaced with IN list was instant 0.00 sec same as the query using …
Post: Flexviews - part 3 - improving query performance using materialized views
April 4, 2011 By 12 Comments
… I described materialized…use it The SQL_API call flexviews.enable() is used to actually build the contents of the view, making it available for querying: mysql…optimization which is required to efficiently refresh MV which use those aggregate functions. Now I delete some line items from orders: mysql…
Post: 3 ways MySQL uses indexes
September 12, 2009 By 27 Comments
… see improved query performance. Beware however of large nested enumerated ranges they are very hard on the optimizer. This just describes how MySQL uses single index – there are more complex rules of how indexes will be used if you…
Post: How Percona does a MySQL Performance Audit
November 24, 2008 By 17 Comments
…levers we can pull for server optimization. Schema and query optimization can give much greater improvements, for… you know the term “thin-slicing.” That describes an experienced consultant’s approach to the…virtually every query.) If it’s not possible to use a patched binary, we can use MySQL Proxy, …
Post: InnoDB vs MyISAM vs Falcon benchmarks - part 1
January 8, 2007 By 117 Comments
… schema and queries are described here Used hardware CentOS release…MySQL version We used MySQL 5.1.14-beta sources for MyISAM / InnoDB and MySQL 5.1.14-falcon bitkeeper tree bk://mysql.bkbits.net/mysql…using index’ is very common optimization we use in our practice. READ_KEY_POINT_NO_DATA_LIMIT Query…
Post: Using Flexviews - part one, introduction to materialized views
March 23, 2011 By 2 Comments
…describing Flexviews. Update: This series has a part 2 and part 3. The output of any SQL query…output of a SQL statement. MySQL even includes a SQL statement… considered to be a performance optimization because: The contents of the…database Delta computation – How it uses those changes to compute the…
Post: Joining on range? Wrong!
May 17, 2010 By 10 Comments
…describe is likely to be around since the very beginning of MySQL, however unless you carefully analyse and profile your queries… A very inefficient approach! MySQL was unable to optimize those simple conditions to …const rows: 206494 Extra: Using where; Using index This query uses 7 bytes of `itm_prd…
Post: Choosing innodb_buffer_pool_size
November 3, 2007 By 38 Comments
…Performance Optimization got a lot of comments …you also have MySQL needs – these include MySQL buffers – query cache, key_buffer, mysql threads, temporary…You may want to make MySQL to use Large Pages for allocating Innodb… things. P.S I only described Innodb Buffer Pool selection for dedicated…
Post: Aligning IO on a hard disk RAID – the Benchmarks
June 9, 2011 By 11 Comments
…) 2 2-2 64 Optimal WT, RA PhysiclDev Type State Errors Vendor Model Size ========== …MySQL versions or settings. Moreover, I have been running these benchmarks for a customer so I just used…= 64M thread_cache_size = 32 query_cache_size = 0 tmp_…benchmark, I’ll just describe few less interesting numbers…
