… MySQL sucks and you better to do things differently or use other database. For example it happens with many types of subqueries… help in many cases to work around MySQL limitations or design application so it uses MySQL strengths but it is other story. I… interesting enough many of these customers stayed with MySQL for other applications or checked MySQL when new version was released. In general…
Post: MySQL 5.6: Improvements in the Nutshell
January 27, 2013 By 16 Comments
…the outline of improvements available in MySQL 5.6 which I thought …Multi-Range-Read (MRR) – Faster ORDER BY nidxcol LIMIT N – Persistent Statistics for Innodb – Improvements to…Innodb – Improvement to Buffer Pool Flushing – Subquery Optimizations – More efficient Optimizer Replication – Optimized ROW…
Post: When the subselect runs faster
March 18, 2010 By 9 Comments
…’||col1=’B')) ORDER BY id DESC LIMIT 20 OFFSET 0; In this case we forcing MySQL to do retrieve rows in sorted… subqueries are not always slow this way is not the most optimal. We do not really need separate subselect to make MySQL… override MySQL index choice: mysql> explain select * from table FORCE INDEX(PRIMARY) where (col1=’A'||col1=’B') order by id desc limit 20…
Post: Are larger buffers always better ?
June 6, 2006 By 9 Comments
… about buffers as “larger is always better” so if “large” MySQL sample configuration is designed for 2GB and they happen to…. The problem was memory allocation – sorting was used in corelated subquery which was executed hundreds of thousands of times but only… run SELECT * FROM LARGETABLE LIMIT 5 MyISAM will allocate the buffer and populate if fully when MySQL will read only 5 rows…
Comment: Using GROUP BY WITH ROLLUP for Reporting Performance Optimization
January 14, 2008 By 14 Comments
In MySQL 5 and up you could do the following. Since you … the counts, and you gain speed by using the limit in the subquery. SELECT grp , sum(cnt) FROM ( SELECT grp , count(*) cnt… slack LIKE “a%” GROUP BY grp ORDER BY cnt DESC LIMIT 10 ) t1 GROUP BY grp WITH rollup; -richard
Post: Shard-Query adds parallelism to queries
November 15, 2010 By 18 Comments
… focused on OLTP workloads. In fact, both Percona Server and MySQL 5.5.7rc have numerous performance improvements which benefit workloads… ’2008-12-31′ GROUP BY Origin ORDER BY c DESC LIMIT 10; Q4 – SELECT carrier, count(*) FROM ontime WHERE DepDelay>10… faster than just running the SQL via MySQL. The remainder of Vadim’s queries use subqueries in the FROM clause, which Shard…
Comment: 7 Reasons why MySQL Quality will never be the same
December 11, 2008 By 15 Comments
… than adding more half baked features. The manor features in MySQL 4.1 were Subselects, Prepared Statements and Character Sets. All… by Monty and these were changes which MySQL architecture was not well designed for. SubQueries had number of bugs in particular in non trivial cases and still have serve performance limitations as of MySQL 5.1…
Post: Missing Data - rows used to generate result set
July 20, 2008 By 15 Comments
… situation. For example if you have clause like TITLE LIKE “%MYSQL%” you may instead use Full Text Search indexes. If you… COUNTRY) C WHERE COUNTRY=’USA’ As of MySQL 5.0 MySQL will materialize the subquery in the from clause fully and so “use… basic “single level” queries keeping in account JOINs, GROUP BY, LIMIT would already be helpful for many cases.
