MySQL using indexes in RAM; why are the disks running?

I have indices of 800M and told MySQL to use 1500M of RAM. After starting MySQL it uses 1000M on Windows 7 x64.

I want to execute this query:

SELECT oo.* FROM table o 
LEFT JOIN table oo ON (oo.order = o.order  AND oo.type="SHIPPED") 
WHERE o.type="ORDERED" and oo.type IS NULL

This finds all items not yet shipped . The execution plan tells me this:

My indices are:

• type_order: Multiple index with type and order
• order_type with order as first index valu开发者_如何学Goe, followed by type

So MySQL should use the index type_order from RAM and then pick out the few entries with the order_type index. I'm expecting only about 1000 non shipped items, so this query should be really fast, but it isn't. Disks are going crazy....

What am I doing wrong?

The query says SELECT sometable.*, so for 1000 matching rows, there will be 1000 fetches of all the fields from the table. Whether the WHERE part indexes are fully loaded into ram or not would only help some. The data fields still have to be retrieved. Odds are, they are scattered all over the disk. So, of course the disk(s) will be doing a thousand small reads.