SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Evangelist
    Join Date
    Jan 2005
    Location
    UK
    Posts
    539
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Slow database section of code

    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:

    PHP Code:
    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. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,270
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    what do 1st and 2nd mean?

    you should consider doing just one query with a join

    are there indexes on any columns?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Evangelist
    Join Date
    Jan 2005
    Location
    UK
    Posts
    539
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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?

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,270
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    would you please do a SHOW CREATE TABLE
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •