… forcing MySQL to do retrieve rows in sorted order and checking if it matches our original WHERE clause with subselects. It looks scary if… subselect to make MySQL check WHERE clause while scanning table in index order. We can just use FORCE INDEX hint to override MySQL….00 sec) This approach works well if WHERE clause is not very selective, otherwise MySQL may need to scan very many rows…
Post: MySQL Query Cache
July 27, 2006 By 70 Comments
…. Well honestly speaking if performance would be problem I should have started with full page caching rather than MySQL level caching but…. Caching full queries only – Meaning it does not work for subselects, inline views, parts of the UNION. This is also common… works with MySQL to add caching but you can enable query cache so it works faster. Low load applications – If you’re…
Post: MySQL EXPLAIN limits and errors.
July 24, 2006 By 6 Comments
… EXPLAIN for problematic queries is very powerful tool for MySQL Performance optimization. If you’ve been using this tool a lot you… practically important one is if Derived tables (selects in FROM clause) are used. MySQL will execute these subselects on explain to provide plan for top most statement, so if you made an error…
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… inside who can figure things out and even patch MySQL if needed. So MySQL went to chase Enterprise market and develop enterprise features… support and various performance related features. I’m not sure if MySQL got back to spend significant resources on taking care of…
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… this case. This most applies to subqueries Where Subselects with IN() become corellated even if they are not, and so using IN…
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 same… solve the same problem having things more modular and using MySQL VIEWs ? mysql> create view user_counts as select user_id,count… from that table restricting by user_id. If this would be handled properly inside MySQL there would be even good reason to…
Post: MySQL and PostgreSQL SpecJAppServer benchmark results
July 25, 2007 By 13 Comments
…’s hard to compare directly).” If you look at Benchmark Results List you would see MySQL Scores 720.56 and PostgreSQL scores… 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: Derived Tables and Views Performance
August 31, 2006 By 16 Comments
…MySQL 4.1, MySQL had support for what is called derived tables, inline views or basically subselects in the from clause. In MySQL….10 sec) Here are couple of explains if you are curios mysql> explain select * from v where i=5; …
Comment: Air traffic queries in InfiniDB: early alpha
November 3, 2009 By 19 Comments
…support the maximum amount of syntax for existing MySQL installations. We have observed that it performs … a dimension table is one obvious solution if disk space is at a premium. We …meaningful information for folks. subselects: Yes, you are correct, we currently do not support subselect. As Robin …
Comment: My Innodb Feature wishes
May 13, 2006 By 30 Comments
… yet avaiable as subselect is not executed. Now if you look at the number of rows query would return without subselect: mysql> select count… executed 55mi times so do not expect fast answer even if subquery is almost instant 
