<story><title>Digg: 40x Performance Increase by Sorting in PHP Rather than MySQL</title><url>http://highscalability.com/blog/2010/3/23/digg-4000-performance-increase-by-sorting-in-php-rather-than.html</url><text></text></story><parent_chain></parent_chain><comment><author>petercooper</author><text>On Feed Digest (a RSS feed processor I sold a few years back but was doing ~300m &quot;digests&quot; per month) I had a similar experience, so I think I know why and what they were doing here..&lt;p&gt;The problem was that even though I denormalized my &quot;posts&quot; (from RSS feeds) table into a &quot;post contents&quot; and a &quot;post metadata&quot; table, MySQL wants to create a &quot;temporary table&quot; to do sorting and pulls every relevant row into it (I found this was even true with indexed columns at the time - I hope they improved it). This can lead to heavy disk access if the number of rows needing to be sorted is too high (consider 100,000 rows of 150 bytes each.. 15 megabytes, not good for a server under heavy load that needs to return a query in &amp;#60; 0.2 seconds).&lt;p&gt;My solution was to pull out ONLY the post IDs and date (or whatever column I wanted to sort by) from MySQL, do the sort in memory, then pull out the posts from the database by already ordered IDs (e.g. SELECT * FROM x WHERE id IN(9,4,22,38,4,..)). MySQL is crazy fast at giving you back rows X, Y, Z, and so on, if you specify them directly.&lt;p&gt;I ended up with many more &lt;i&gt;fast&lt;/i&gt; queries rather than fewer deathly slow queries, and that was a great tradeoff in the end. I believe the system still runs that way under its new owner.</text></comment>
<story><title>Digg: 40x Performance Increase by Sorting in PHP Rather than MySQL</title><url>http://highscalability.com/blog/2010/3/23/digg-4000-performance-increase-by-sorting-in-php-rather-than.html</url><text></text></story><parent_chain></parent_chain><comment><author>brutimus</author><text>This quote rubs me the wrong way a little...&lt;p&gt;&quot;The relational database tool chain is not evolving. It has failed for large scale, real-time environments.&quot;&lt;p&gt;I&apos;ve been a big user of Postgres for going on five years now and it has made giant leaps in both features and  performance.  You can&apos;t blindly say relational tools are done evolving.  I know the other players have made a lot of progress lately too (Oracle, MySQL, etc).</text></comment>