SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Enthusiast Mickj's Avatar
    Join Date
    Jun 2002
    Location
    Margate, Kent, UK
    Posts
    52
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Can Somebody get some extra speed out of this SQL

    Code:
    "SELECT a.artist_id,a.artist_name,a.is_feat,a.a_fid,a.a_fname,a.the,c.record_id,c.code,c.chart,c.pos,c.last,c.pos-c.last as calc
    				,c.label,c.c_sales,r.r_rating,r.r_votes,r.r_title,r.r_record_id,r.r_lyric_id,r.num_discs,r.r_comments,r.r_cover_id,r.r_no_coms
    				,r.r_re,r.r_ri,r.r_wks,r.r_charted,r.r_status,r.r_pk,r.r_sales,r.r_sales_added,r.r_analist,r.r_type
    				,p.pic_linked,p.pic_id,p.pic_small,d.gallery_pic_id,l.a_id as lead_artist,an.an_current,an.an_list,an.an_list1,an.an_list2,an.an_list3
    				FROM ibf_charts c
    				INNER JOIN ibf_artists a ON (a.artist_id = c.artist_id)
    				INNER JOIN ibf_records r ON (r.r_record_id = c.record_id)
    				LEFT JOIN ibf_update_analysis an ON (an.an_chart_id = c.dj_id)
    				LEFT JOIN ibf_artists_and_records l ON (l.r_id = c.record_id AND l.lead=1)
    				LEFT JOIN ibf_record_discs d ON (d.record_id = r.r_record_id)
    				LEFT JOIN ibf_gallery_pics p ON (p.pic_id = d.gallery_pic_id)
    				WHERE {$tz}c.code = '" . $this->this_week . "' AND c.chart = '" . $chi . "'{$tp} LIMIT $p,$perpage"
    $tp is the Grouping and ordering set depending on the request and $tz may be an NOT IN (Clause) Again depending on the users request i.e show droupouts from last weeks charts.

    This works fine without any server problems even when being hit 30-40 times a min but can I get any more mph out of it all fields are required as a number of format functions use them for the display.

    thanks

    mick
    Be Proud, Be Loud, Be Heard.
    Systems designed by me
    Battle Of The Bands
    http://www.database-dreams.co.uk/Com...p?act=artchart
    Worldwide Music Charts
    http://www.database-dreams.co.uk/Com...ex.php?act=RCH
    UK Charts history (Started 9 Setember 01)
    http://www.database-dreams.co.uk/Com...php?act=charts

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    move your LEFT JOIN to ibf_artists_and_records to be right after the first two INNER joins, and change it to INNER join as well, because that's what it in effect is, since you stipulate l.lead=1

    make sure all the columns used as foreign keys in joins all have indexes defined on them

    but NOT IN were you referring to a list of id values, or a subquery? because if the latter, use another LEFT JOIN instead, testing for NULL in the right table
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Enthusiast Mickj's Avatar
    Join Date
    Jun 2002
    Location
    Margate, Kent, UK
    Posts
    52
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937
    move your LEFT JOIN to ibf_artists_and_records to be right after the first two INNER joins, and change it to INNER join as well, because that's what it in effect is, since you stipulate l.lead=1

    make sure all the columns used as foreign keys in joins all have indexes defined on them

    but NOT IN were you referring to a list of id values, or a subquery? because if the latter, use another LEFT JOIN instead, testing for NULL in the right table
    Thanks I Can't Change the ibf_artists_and_records as there may not always be a artist marked as leader where there is a combination of artists that relate to a recording the sites desined to check that and create links correctly depending in that value.

    I think this one can be changed as just thought the only time there aint any related records in the analysis table is when I clear it and add the new chart details after that it would always have related records so will change that

    LEFT JOIN ibf_update_analysis chancing to Inner

    The NOT IN Clause is the result if a function that as you say gives me a list of values a,s,d,f,g,g and so on but because it need to return specific details that have some heavy filtering done by the function I dont think a sub query would work as I may also need to add a completely new display as it is only used when somebody wants to view specific data.

    The who thing took me neally a year to get right may be if I had come here more ofton and got your help it would only have taken half that I'm trying to make improvments where I can but the whole thing is so complexe in it's structuure it's hard trying to put it into words as I aint even got to the discs section which I think is my best work to date.

    I'll update that left join and all indexes are in place as the one I found yesterday seems to have given a small speed impovement so with both it should Be going as best it can.


    many thanks for all your help

    I'll post the main Dics tracks one later so you can pic at it for me if ya don't mind.

    mick
    Be Proud, Be Loud, Be Heard.
    Systems designed by me
    Battle Of The Bands
    http://www.database-dreams.co.uk/Com...p?act=artchart
    Worldwide Music Charts
    http://www.database-dreams.co.uk/Com...ex.php?act=RCH
    UK Charts history (Started 9 Setember 01)
    http://www.database-dreams.co.uk/Com...php?act=charts


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
  •