Why is MySQL still a toy database

I have been using MySQL for a very intensive read-write web application (averaging 102 queries per second) for more than two years. I had ups and downs with it, like crazy MyISAM behavior that readers can block writers AND OTHER READERS. Basically, a table level lock is issued for read. I have 100+ million records in a table, so it takes a while to find anything that is not indexed. In the meantime, users are pondering (102qps, remember) and load goes up so much because of web server processes queuing like crazy. Ok, I learned not to do that anymore. I now use binary logging, restore to a different server and query there. Maybe a switch to InnoDB would be a good idea, but in this case I'd rather use a serious MVCC database like Firebird. Why, you might ask... well, here's one of many reasons, the one that prompted my to write this:

In Firebird, I can happily do this:

delete from atable a1
where exists (
    select 1 from atable a2 where a1.data = a2.data and a1.id <> a2.id );

It just does it, and fast, because index on primary key field ID is used. In MySQL, to quote the manual:

Currently, you cannot delete from a table and select from the same table in a subquery.

Come on, this is one of the most basic database operation. So, what am I now to do? Waste my time dumping the list of IDs to delete to some temporary location, and then iterating that list to delete. :(

Tweet to @mbabuskov Milan Babuškov, 2011-12-01