Multiple engines in the MySQL case are an anti-feature, from the MyISAM v InnoDB perspective. Far too often the wrong or arbitrary choices are made, mixing within the same database, causing unnecessary confusion and work. "That table is on this storage engine which does not support the operation we need."

I can see the case where a different storage engine supports a different data format or use case (columnar storage for example), but in an RDBMS case I would argue consistency outweighs convenience.