Put on your seat belt; this will be a E-ticket ride!
> The only difference between the first and second query is ...
Not necessarily!
When timing a query, add SQL_NO_CACHE and run the query twice. Use the second timing.
The fist time you run a query on a table, it will need to fetch data from disk. The second query (same one, or different one hitting similar rows) will find everything cached in ram and run typically 10 times as fast. That _may_ explain 0.42 vs 0.05 (8 times as fast).
Avoiding the Query cache is also necessary.
> `description` text NOT NULL,
The datatype here turns out to be critical in this discussion. You picked the worst field for this benchmark. The gory details are here:
http://dev.mysql.com/doc/refman/5.0/en/internal-temporary-tables.html
Summary: Query 2 could create a MEMORY temp table; Query 1 had to use MyISAM, thereby paying some penalty.
Query 3 _may_ be an example of where a subquery is faster than the equivalent JOIN. I say _may_ because it may be 'fast' because of the caching discussed above.
Yet another factor...
> PRIMARY KEY (`id`),
> KEY `msrun_peaks_ibfk_1` (`msrun_id`),
> ) ENGINE=InnoDB
In InnoDB, any secondary key implicitly includes the columns of the PRIMARY KEY. That is, the KEY above is really (msrun_id, id). Combine that with the fact that you need _only_ those two fields in the query. This makes the index a "covering" index. Each index is stored in its own BTree. With a covering index, only the index need be accessed, not the data. This is sometimes a big win.
Run the timings again. Change to a non-text, non-covered field. Provide the EXPLAINs. And SHOW TABLE STATUS. The EXPLAINs may validate some of what I am saying ("Using index" == 'covering index').
I may have left out some issues, but that should get you closer to unserstanding _this_ apparent anomoly.