… query. Reporting with group by over 10.000.000 of rows in real time is one painful thing to do requiring complex… need arises but we still can’t deny – MySQL does not handle complex queries well, being able to use only one CPU effectively on single node and no true federation support. 4. Be Solid MySQL…
Post: MySQL EXPLAIN limits and errors.
July 24, 2006 By 6 Comments
… problem if you examine your queries manually but if you have automatic tool which catches possibly complex queries it can give you head… about functions cost Since MySQL 5.0 you may use user functions in your query which may also perform queries themselves. These actions… table like output format is good but with growing MySQL Server complexity it is getting impossible to present all relevant information this…
Post: Feature Idea: Finding columns which query needs to access
November 17, 2006 By 16 Comments
… it is often interesting which columns query needs to access to provide result set as it gives …, not to mention more complex questions of subselects. It would be great tool for MySQL Performance Optimization if MySQL Server could show this… other clauses such as GROUP BY and ORDER BY. For complex queries especially if they do not refer to the columns with…
Post: How to find MySQL queries worth optimizing ?
September 11, 2012 By 6 Comments
… find out queries which should be optimized. By looking at pt-query-digest report it is easy to find slow queries or queries which… this query run better ? The full answer to this question will indeed require complex analyses as there are many possible ways query can… which are found and returned up to the top level MySQL part for processing are counted the Rows_examined remains zero…
Post: Eventual Consistency in MySQL
November 18, 2011 By 8 Comments
…of foreign key relationships in any complex database. We can find all foreign keys by querying the INFORMATION_SCHEMA. The KEY_COLUMN…generate quality control queries automatically, we can run them at any time: $ cat generate_foreign_key_checks.sql | mysql -N | mysql -E *************************** 1…
Post: MySQL 5.6.10 Optimizer Limitations: Index Condition Pushdown
March 14, 2013 By 2 Comments
… more complex optimization strategies. Even row-level operation counters are sometimes not enough. Do you want to know more about the MySQL 5.6 query optimization improvements in a practical way, with real-life…-party, independent and technical opinion about the new features of MySQL query planner? Are you not yet familiar with terms like MRR…
Post: Percona XtraDB Cluster: Multi-node writing and Unexpected deadlocks
August 17, 2012 By 17 Comments
… mysql> insert into autoinc (j) values (‘node2′ ); Query OK, 1 row affected (0.00 sec) node2 mysql> insert into autoinc (j) values (‘node2′ ); Query OK, 1 row affected (0.01 sec) node2 mysql> insert into autoinc (j) values (‘node2′ ); Query… this problem? Here are some ideas from simplest to most complex: Only write to a single node in the cluster (at…
Post: Apache PHP MySQL and Runaway Scripts
May 20, 2008 By 20 Comments
Sometimes due to programming error or due to very complex query you can get your PHP script running too long, well … – for example you can open another MySQL connection to kill the query which was running (otherwise query may well continue to run even… for 1000 seconds (spending this time on MySQL time) one is doing single query another 1000 queries 1 second length each. Neither of…
Post: MySQL Optimizer team comments on TPC-H Results
April 14, 2008 By 1 Comment
… set of complex queries which use SubQueries. So what is the reason ? Why TPC-H queries are not targeted ? The reason is MySQL Optimizer… goal of optimizing TPC-H queries or queries from any other benchmarks, rather they look at the queries which MySQL Users have problem with… into final MySQL 6.0 release. Summary: Poor MySQL 6.0 performance on TPC-H queries does NOT mean to quantify MySQL Optimizer Team…
Post: Does Slow query log logs all slow queries ?
June 25, 2007 By 3 Comments
… slow queries to catch problematic queries or for audit purposes. In fact however not all the queries are logged. I already mentioned mysql slave queries… trust standard slow query log – some queries may be omitted from it. Also if someone would like to hide complex queries from you it…
