Optimizing Select SQL request with millions of entries

Question

I'm working on a table counting around 40,000,000 rows, and I'm trying to extract first entry for each "subscription_id" (foreign key from another table), here is my acutal request:

SELECT * FROM billing bill WHERE bill.billing_value not like 'not_ok%' 
AND
   (SELECT bill2.billing_id
      FROM billing bill2
      WHERE bill2.subscription_id = bill.subscription_id 
      ORDER BY bill2.billing_id ASC LIMIT 1
    )= bill.billing_id; 

This request is working correctly, when I put a small limit on it, but I cannot seem to process it for all the database.

Is there a way I could optimise it somehow ? Or do things in an other way ?

Table indexes and structure:

Indexes:

Answer 1

I haven't tried it, because I don't have an MySQL DB at hand, but this query seems much simpler:

select * 
from   billing
where  billing_id in (select min(billing_id)
                      from   billing
                      group by subscription_id)
and    billing_value not like 'not_ok%';

The inner select get the minimum billing_id for all subscriptions. The outer gets the rest of the billing record.

If performance is an issue, I'd add the billing_id field in the third index, so you get an index with (subscription_id,billing_id). This will help for the inner query.