Can Somebody get some extra speed out of this SQL

[Mickj]

"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

[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

[Mickj]

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