Michel,

How about splitting the query in two ?

SELECT NoMember, MAX(Creation) MaxCreation;
       FROM Login ;
       WHERE NoMember NOT IN (
             SELECT DISTINCT noMember ;
                    FROM Login ;
                    WHERE Creation >= DATE() - 120 AND ;
                          BETWEEN(NoMember, lnNumerstart, lnNumberEnd));
       WHERE BETWEEN(NoMember, lnNumerstart, lnNumberEnd) ;
       GROUP BY NoMember ;
       INTO CURSOR NonActiveMembers

SELECT M.*, MaxCreation ;
       FROM Member M INNER JOIN NonActiveMembers N ON N.NoMember=M.Numero ;
       WHERE Member.mailing = 1 
       INTO CURSOR Temp

Tough this strategy actually executes three queries, it might be faster as the Active Members are filtered out before the group by. Whether it improves performance might depend on the balance of Active and nonActive members.

If the selection made by Numero and mailing is low (percent), you can also try it the other way arround:

SELECT * ;
        FROM Member
        WHERE BETWEEN(Numero, lnNumerstart, lnNumberEnd) AND mailing = 1 ;
        INTO CURSOR MemberSelection

SELECT M.*, MAX(Creation) as LoginCreation ;
        FROM MemberSelection M INNER JOIN Login L ;
             ON Numero = NoMember ;
        GROUP BY Numero ;
        HAVING Creation < DATE() - 120 ;
        INTO CURSOR Temp

In either case you'll need the following indices:
- Numero
- NoMember
- Creation
- Mailing (Usefullness depends on selectivity)


HTH,

Walter,

>I would like to optimize this query.
>
>

>SELECT MEMBER.*,MAX(LOGIN.CREATION) AS LOGINCREATION FROM LOGIN;
> INNER JOIN MEMBER ON LOGIN.NOMEMBER=MEMBER.NUMERO;
> WHERE MEMBER.NUMERO>=lnNumberStart AND MEMBER.NUMERO<=lnNumberEnd;
>  AND MEMBER.MAILING=1;
> HAVING LOGINCREATION< DATE()-120;
> GROUP BY 1 INTO CURSOR TEMP
>