Slow database section of code

PHP
1

I have a mysql table of 100+ curr ency values dating back some time giving some 30k+ rows.

Taking any particular curr ency combo:
1/ i loop through all rows selecting all the rows applicable to the 1st
2/ then within each loop select the 2nd’s value for that date
3/ calculate the combined value
4/ print out the value for the pair on each date.

Below is some sudo code to give principle:

select * from vals where c=1st order by dateset DESC
 while....{
      get $thisdateset
      select * from vals where c=2nd and dateset=$thisdateset
            while....{....
             }
    }

Problem is that this is taking over 9 seconds just this section of the code!
Trying to think of best approach here. Any ideas?

I have ruled out storing the combination values every day as this would mean 10k rows every day, plus would take ages to complete via cron daily.
I also thought about writing it all to a text file, but same applies really.

2

1st and 2nd are the paired currs.
I’ve just changed the ISO column to CHAR (with length 3) instead of VARCHAR, and also created an index on that column and also the dateset column.
This has massively speeded it up, although, i’m wondering if the way i set up the indexes is correct. I use a windows prog called SQLyog and selected manage indexes. I then simply added a new index on each column individually, but didn’t select any options such as Primary, Unique or Fulltext.
Not sure how I would do a JOIN in this case?

3

what do 1st and 2nd mean?

you should consider doing just one query with a join

are there indexes on any columns?

4

would you please do a SHOW CREATE TABLE