… we forcing MySQL to do retrieve rows in sorted order and checking if it matches our original WHERE clause with subselects. It… subselect to make MySQL check WHERE clause while scanning table in index order. We can just use FORCE INDEX hint to override MySQL index choice: mysql> explain select * from table FORCE INDEX(PRIMARY) where (col1…
Comment: My Innodb Feature wishes
May 13, 2006 By 30 Comments
… avaiable as subselect is not executed. Now if you look at the number of rows query would return without subselect: mysql> select count…
Post: Top 5 Wishes for MySQL
June 29, 2007 By 10 Comments
… to mention other issues. I’m saying about SubSelects which were added in MySQL 4.1 but you still could not run… saying about XA which were released in MySQL 5.0 without consistent work with MySQL close Partner – Innobase to ensure Group Commit… inside who can figure things out and even patch MySQL if needed. So MySQL went to chase Enterprise market and develop enterprise…
Post: MySQL VIEW as performance troublemaker
August 12, 2007 By 57 Comments
… them it turn used subqueries subselects and derived tables. It is also very dangerous if you assume MySQL would optimize your VIEWs… you get number of comments left by the given user ? mysql> select count(*) from comments where user_id=5; +———-+ | count(*) | +———-+ | 1818… solve the same problem having things more modular and using MySQL VIEWs ? mysql> create view user_counts as select user_id,count…
Post: MySQL and PostgreSQL SpecJAppServer benchmark results
July 25, 2007 By 13 Comments
… databases) is not where most of MySQL sweet spots come from. MySQL performance gains usually come from MySQL unique features – non transactional MyISAM… in MySQL. For example PostgreSQL is slower at connection creation and MySQL is very fast, while SubSelect optimization is very bad in MySQL for many cases until MySQL 5.2
Post: MySQL Query Cache
July 27, 2006 By 70 Comments
… great feature called “Query Cache” which is quite helpful for MySQL Performance optimization tasks but there are number of things you…. Caching full queries only – Meaning it does not work for subselects, inline views, parts of the UNION. This is also common… lets speak a bit about Query Cache configuration and mainance. MySQL Manual is pretty good on this: Query Cache Query Cache…
Post: MySQL EXPLAIN limits and errors.
July 24, 2006 By 6 Comments
… query execution, sometimes being dynamic based on data. With EXPLAIN MySQL kind of simulates execution but it obviously does not access… Derived tables (selects in FROM clause) are used. MySQL will execute these subselects on explain to provide plan for top most statement… how many rows will be examined MySQL will still print full number. Here is example: mysql> explain select * from lt limit 10…
Post: MySQL Optimizer team comments on TPC-H Results
April 14, 2008 By 1 Comment
… any other benchmarks, rather they look at the queries which MySQL Users have problem with and first target the most common… Tables” AKA “Subselects in FROM Clause” are not optimized yet even though some optimizations may make it into final MySQL 6.0 release. Summary: Poor MySQL 6.0 performance on TPC-H queries…
Post: Database problems in MySQL/PHP Applications
August 11, 2006 By 13 Comments
… Store optimization, and I’m now on my own, offering MySQL and LAMP Consuilting Services. 2. Not using auto_increment functionality… by people with traditional database background. Things are different with MySQL. There are many successful applications, using tens of thousands of… queries in this case. This most applies to subqueries Where Subselects with IN() become corellated even if they are not, and…
Comment: My Innodb Feature wishes
May 14, 2006 By 30 Comments
… could be Optimizer bug with subselect execution or with explain. Please report it at http://bugs.mysql.com This query should be… are VERY many missing optimizations with Subselects at this point. You should be very careful migrating Subselects from other Database servers as… how you can rewrite this query to perform well on MySQL.
