How to Select and make use of the indexes

Hi all,

I'm trying to find if its possible to utilise the indexes so my query doesnt spend 2 hours sorting itself before rows are returned.

If I run this, rows are returned instantly;
SELECT  subs.ID, subs.customer, subs.contract,  subs.ref, subs.sequence FROM subs

if I add this it orders it first before returning the rows;
order by   subs.ref, subs.sequence

However, if I add this rows are returned instantly because there is an index for that order;
order by subs.customer, subs.contract,  subs.ref, subs.sequence

This is where i get a bit stuck.  I can't seem to link the table to itself and still have it return rows instantly.  It orders the linked table without using the indexes so goes back to taking 2 hours before returning any rows.
SELECT     subs.subscription_id, renewals.subscription_ID FROM subscription.subscription subs
LEFT OUTER JOIN subscription.subscription AS renewals ON subs.subscription_ref = renewals.subscription_ref AND subs.subscription_sequence + 1 = renewals.subscription_sequence
order by subs.subscription_customer, subs.subscription_magazine,  subs.subscription_ref, subs.subscription_sequence

To make matters worse I actually need to link it to itself twice.
I need to bring in fields from the rows in the table for the 1st in the sequence and the next in the sequence.  Example;
I'll use just the ID for what I need to include, but there are actually 4 fields so I can't use a sub query.
ID,      customer,      ref, sequence, acquisitionID, renewalID
25,     myCompany,  pro1,  1,               25,               340
340,   myCompany,  pro1,  2,               25,               700
700,   myCompany,  pro1,  3,               25,               1020
1020, mycompany,  pro1,  4,               25,               NULL

My current SQL for this is as follows;
SELECT      subs.subscription_id FROM         subs LEFT OUTER JOIN
   subs AS renewals ON subs.subscription_ref = renewals.subscription_ref AND
   subs.subscription_sequence = renewals.subscription_sequence LEFT OUTER JOIN
   subs AS acquisitions ON subs.subscription_ref = acquisitions.subscription_ref AND
   subs.subscription_acquisition_sequence = acquisitions.subscription_sequence AND acquisitions.subscription_term_type = 'ACQUISITION' ORDER BY subs.subscription_customer, subs.subscription_magazine, subs.subscription_ref, subs.subscription_sequence

Any help would be much appreciated.

Regards,
KintonStart Free Trial