>Hi all,
>
>I have a query that I want to optimize with rushmore index:
>
>
>Select fondsen.*,koers_hist_tot.dat_krs;
>from fondsen;
>left outer join koers_hist_tot;
>on fondsen.fonds_cd = koers_hist_tot.fonds_cd;
>where koers_hist_tot.dat_krs = dat_e_bkp
>
>
>This query takes 200 seconds. Fondsen has 5 million records and koers_hist_tot 60,000 records.Dat_krs is a date,dat_e_bkp a constant. I don't want to index on fondsen because that takes too long. Is it possible to speed it up?
>
>Thanks beforehand.
1) With this query you only will get records with dat_krs from koers_hist_tot = dat_e_bkp, so there is no need to use left join, it is essentially inner join anyway.
2) You may first get koers_hist_tot.dat_krs, koers_hist_tot.fond_cd from Koers_Hist_tot and then use a second select to get records from the big table, e.g. in VFP9 you may do
select fondsen.*, InnerSel.dat_krs from Fondsen inner join (select koers_hist_tot.dat_krs, koers_hist_tot.fond_cd where ...) InnerSel on
FondSen... = InnerSel..
In VFP5 you can use two selects.
If it's not broken, fix it until it is.
My Blog