Really? I've done many, many queries on large, but fittable-in-memory datasets and with a few little tricks even MySQL can be fast (make everything a temporary table with an append only transaction table to rebuild the temporary tables if lost, don't use keys, use indexes, avoid bad practices (filtering after a join, using subselects, or needlessly avoiding HAVING clauses).
Did you mean large, fittable-in-memory, and (distributed across multiple computers and/or very reliable)? Because that is much harder.
Can you explain this further? How is using a temporary table faster? Explicit temporary tables are simply regular tables that only last your current session. Do you mean the MEMORY storage engine? What do you mean by "don't use keys, use indexes?" The term KEY and INDEX in MySQL are exact synonyms.
Temporary tables default to being mapped in memory and for more complex querying (where you have multiple queries and conditionally select further queries depending on results) are faster since the table that they are based off of could have changed, but they will not, so caching is much more likely. Keys and Indexes are not exact synonyms. Give it a try some time, do a where clause with mod(id, 5) = 0 on a keyed table and on an indexed table. The indexed table will be non-trivially faster (I've seen 3x faster).
You are clearly not talking about MySQL. CREATE TEMPORARY TABLE simply creates a physical table on disk that is destroyed at the end of the session, which can be created with any storage engine. Explicit temporary tables are handled just like "regular" tables and is dependent upon the storage engine (w/ MyISAM, indexes are stored in the key buffer, InnoDB keeps both recently accessed data and indexes in the InnoDB buffer pool).
Perhaps you are referring to primary keys vs secondary indexes, for which there is a difference in how they are physically stored on disk and in memory, particularly with InnoDB. There is a substantial performance gain to be had by having a relevant primary key that is referenced in the where clause as the data is stored in the same page as the primary key, both on disk and in the buffer pool. Secondary indexes reference the primary key, thus require two lookups.
Another key point is that temporary tables are horrible for concurrency. Obviously only one session can access them, but more importantly, they end up causing a great deal of disk activity. At the end of every session, the table is physically dropped. In ext3, this can take quite a while for large tables (not so bad in filesystems like xfs). It also can cause substantial stalls in InnoDB as the buffer pool is scanned for matching pages and are flushed out. Percona Server has innodb_lazy_drop_table to mitigate this issue to some degree, but dropping tables is still always an expensive operation. This is mostly a concern for OLTP, not necessarily OLAP servers.
Did you mean large, fittable-in-memory, and (distributed across multiple computers and/or very reliable)? Because that is much harder.