… can result in a lot of Random I/O. The purpose of MRR is to reduce this Random I/O and… against base table, the number of these point primary key lookups will be depend on the number of rows that match the … in case of MySQL 5.5 and ~5 hours in case of MySQL 5.6 and MariaDB 5.5. Note that query cache…
Post: Using VIEW to reduce number of tables used
September 28, 2007 By 12 Comments
… couple of small rows, but require at least 16K page in Innodb), keeping all tables open in Innodb dictionary and number of other… is very tempting to use VIEWs to reduce number of tables dramatically – merging say 1000 of users to the same table as VIEWs are…. The VIEW approach works for SELECT, UPDATE, DELETE queries but not for INSERT: mysql> insert into post123 values(1,”aaa”,”bbb”); ERROR…
Post: MySQL EXPLAIN limits and errors.
July 24, 2006 By 6 Comments
…is generated as a part of query execution, sometimes being dynamic based on data. With EXPLAIN MySQL kind of simulates execution but it …which reduces number of passes MySQL had to perform and so number of row combinations really examined. LIMIT is not taken into account while estimating number of …
Post: Modeling MySQL Capacity by Measuring Resource Consumptions
March 8, 2011 By 1 Comment
… and user CPU usage of MySQL process in 1/100 of the second. (This is… and correlating to number of queries running will give average CPU usage per query. If you…reduce wait time and number of IOs per query as well as increase CPU time spent. This is because IO handling requires significant number of…
Post: Should we give a MySQL Query Cache a second chance ?
April 10, 2011 By 63 Comments
… MySQL 5.5 uses for Innodb Buffer Pool and have multiple MySQL Query Cache instances hashed by query hash. It would help to reduce complexity… can be done for rather large set of applications. Many applications in MySQL have very large number of queries doing updates through primary key and…
Post: MySQL Query Cache
July 27, 2006 By 69 Comments
…number of things you need to know. First let me clarify what MySQL Query Cache is – I’ve seen number of people being confused, thinking MySQL Query…of usable memory – Queries are constantly being invalidated from query cache by table updates, this means number of queries…
Post: Shard-Query adds parallelism to queries
November 15, 2010 By 16 Comments
… MySQL so that is could better utilize all available resources (that is, scale better) when running small numbers of queries which examine a lot of… a range of records. This table is partitioned by month which means that MySQL can use partition pruning to reduce the amount of data…
Post: On Good Instrumentation
May 31, 2010 By 1 Comment
… to see number of mysql calls in addition to MySQL response time. This helps to understand if it is the issue with number of queries or their… gets with standard Ethernet network and finding a ways to reduce number of queries is going to be more helpful. The Glue Our applications…
Post: MySQL 4 to MySQL 5 Upgrade performance regressions
June 6, 2007 By 8 Comments
… “inside innodb”. I wish there would be some stats for number of queries waiting to be committed but there is not one easily… away to many updates outside of transactions in auto_commit mode. Wrap them in transactions and reduce number of commits if you can. It is great optimization idea anyway. Also please do not read this post as MySQL 5…
Post: Database access Optimization in Web Applications.
September 12, 2006 By 10 Comments
… really matters is amount of rows are analyzed by the query. For example SELECT COUNT(*) FROM links WHERE domain = ‘mysql.com’; will return… different types and number of columns) – this is not a good practice. However if you can reduce number of queries without a lot of complications to application…
