sql (mysql) optimize query oder schema, avoiding full table scan

Question

I have one table with data. The table has entries for orders, each order has some types (or state e.g ordered, ..., polished, packed, shipped).

Now, I want to do this query.

select * from orders as o 
  where not exists
  (SELECT * from orders as oo 
    where 
    o.order = oo.order and 
    oo.type="SHIPMENT")

type and shipment have a index, but it is only used after doing a full scan. So the query takes far to long. I want to present the data directly.

Answer 1

Having an index on orders.type doesn't necessarily mean that this index will be used. In fact, the index is not used if it's not selective enough. Also, mysql works a little faster if you use NOT IN or LEFT JOIN/IS NULL approach instead of NOT EXISTS :

// LEFT JOIN/IS NULL:
SELECT o.* 
FROM orders o
LEFT JOIN orders oo ON (oo.order = o.order AND oo.type="SHIPMENT")
WHERE oo.id IS NULL