In a comment thread on a recent post comparing MySQL to Postgres, Jonathon Moore chimed in based on experience with both products. His characterization of some MySQL problems:
Regardless of what back end you choose the mysql query engine is week. It only supports nested loop scan, no merge join, no hash join, nothing fancy. Further sub quires can’t even use indexes so they are near useless. The rule when using mysql is *don’t join*. So the case where mysql can be put to good use is where all your quires are over a single table, which is why it has done well for web apps I wold suspect.
was similar to those in a mid-2006 post on MySQL Performancing Blog, which said:
One of the reasons elevating this problem in MySQL is lack of advanced join methods at this point (the work is on a way) – MySQL can’t do hash join or sort merge join – it only can do nested loops method which requires a lot of index lookups which may be random.
What’s the current situation, mid-2008? Surely data warehousing storage engines such as Infobright’s aren’t so artificially limited in their join strategies.