June 18, 2013

Post: Flexviews - part 3 - improving query performance using materialized views

April 4, 2011 By 20 Comments

…result set at one…set (43 min 10.11 sec) As an added benefit, if you build more than one complete refresh view from an incrementally refreshable one…mysql> delete -> from order_lines -> where order_id -> between 1 and 100…

Filed Under: Insight for DBAs, MySQL

Post: Why you should ignore MySQL's key cache hit ratio

February 28, 2010 By 28 Comments

…possible, no more than 1:100” or … query’s execution time. Alas,…one interesting question that we haven’t really addressed yet. How bad is bad? This is where we return…results to your IO system’s capabilities. MySQL doesn’t have good instrumentation for scientifically choosing a key_buffer_size setting…

Filed Under: Insight for DBAs, MySQL Tagged With: , , ,

Post: Shard-Query EC2 images available

May 11, 2011 By 2 Comments

…more than one…(100…returned (0.084244966506958s, 0.078309059143066s, 0.0059359073638916s) Execute the test: As seen above, the run_query script will run one more more…results cd shard-query/ $ ./run_query < queries.sql | tee raw |./pivot_results…mysql wait-timeout=86400 To be continued You can now set…

Filed Under: MySQL

Post: InnoDB Full-text Search in MySQL 5.6 (part 1)

February 26, 2013 By 1 Comment

…results between MyISAM FTS and InnoDB FTS over the same data sets… NOT NULL, `full_name` varchar(100) DEFAULT NULL, `details` text…Now, let’s return to all of …more than one at a time. If you try it, this is what happens: mysql… I ran OPTIMIZE TABLE several more times, and each execution took between…

Filed Under: Insight for DBAs, Insight for Developers, MySQL Tagged With: , , , , ,

Post: Identifying the load with the help of pt-query-digest and Percona Server

December 29, 2011 By 3 Comments

… time period remember to set it back to a value greater than 0, because otherwise you…# Full scan 100% yes, 0% no # String: # Databases wp_blog_one (264/84%), wp_blog_tw… (36/11%)… 1 more #…execution plan used by MySQL. The end result might be that you end up limiting the number of results returned…

Filed Under: Insight for DBAs, MySQL

Post: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5

March 21, 2012 By 10 Comments

… returned …one or more range primary key lookup. Thereby, converting Random access to one or more sequential access. There is one… Benchmark results For…execute, ~11 hours in case of MySQL 5.5 and ~5 hours in case of MySQL…is set …MySQL 5.6 and MariaDB 5.5 are both reading more rows than MySQL… 100.00 …

Filed Under: Benchmarks, Insight for DBAs, MySQL Tagged With: , , , , , , , , , , , , , ,

Post: Heikki Tuuri Innodb answers - Part I

October 26, 2007 By 19 Comments

… compresses the database more than just prefix compression… ahead and results were significant. …execution is not serializable. PZ: Cool. This is one of little known new features in MySQL…returns in less than 100 microseconds. And if you do not have, then the only way to get good performance is to set…

Filed Under: Insight for Developers Tagged With: ,

Post: Database access Optimization in Web Applications.

September 12, 2006 By 10 Comments

…return you only information you need. Check number of rows which are used to generate result set…result set. Such queries need to be redesigned rather than…more rows because it is optimized differently. One of typical examples here would be SELECT * FROM tbl WHERE id=5 executed…

Filed Under: Insight for Developers Tagged With:

Post: When the subselect runs faster

March 18, 2010 By 9 Comments

… This query took more than…return them in the right order. MySQL can chose only one of them to execute…executed enough times to produce 20 rows in result set…result time: (20 rows in set (0.01 sec)) So by rewriting query using subqueries we actually improved it performance 100…

Filed Under: Insight for Developers, MySQL Tagged With: , , , , ,

Comment: Database problems in MySQL/PHP Applications

August 11, 2006 By 13 Comments

… but using prepare/execute is more consistent with what …query and external caching of result sets is generally a…MySQL you might be > better of using several queries than doing complicated ones. Of > course you would rather use IN() than do 100…are scanned and returned. SELECT * FROM articles INNER…

Filed Under: Insight for Developers Tagged With: , ,
« Older Posts