SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  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)

    Would somebody be kind and look at this SQL

    This is the second version of the query as the first took my site down with MYSQL locked process so hope somebody can let me know if they can see any problems

    Description:
    artists_records is a many to many table and at precent contains 27,000+ entrys
    artists is being used as a lookup just to get the artists name contains 13,000+ Entrys
    charts is the main charts table which is being used to get the total number a record has been at No 1 Contains 83,000+ Entrys

    But not sure wether i Should be useing the Artists ID in the charts table now

    You can fiind other examples and other funtions results here http://www.database-dreams.co.uk/Com...0&c=1&site=181
    Code:
    function get_question_18($chr) {
    					 global $ibforums, $DB;
    					 $l = 0;
    					 // Question: Which Artists Have Spent The Most Weeks At No1
    					 $q = $DB->query("SELECT ar.a_id,a.artist_name,a.the,COUNT(c.id) AS c_ones
    				FROM ibf_artists_and_records ar
    				INNER JOIN ibf_charts c ON (c.record_id = ar.r_id AND c.chart = '" .$chr. "' AND c.pos='1')
    				INNER JOIN ibf_artists a ON (a.artist_id = ar.a_id)
    				WHERE ar.is_feat=0 GROUP BY ar.a_id ORDER BY c_ones DESC LIMIT 50" );
    				while( $d = $DB->fetch_row($q) ) {
    						$l++;
    						 $data .= '&nbsp;&nbsp;<font size=2>'.$l.' '.$d['artist_name'].' - Has Spent A Total Of '.$d['c_ones'].' Weeks At Number One</font><br>';
    				}
    				return $data;
    			 }
    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,016
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    just a few minor changes --
    Code:
    select ar.a_id
         , a.artist_name
         , a.the
         , count(*) AS c_ones
      from ibf_charts c 
    inner 
      join ibf_artists_and_records ar
        on c.record_id = ar.r_id 
       and ar.is_feat = 0 
    inner 
      join ibf_artists a 
        on ar.a_id = a.artist_id
     where c.chart = '" .$chr. "' 
       and c.pos = '1'
    group 
        by ar.a_id 
         , a.artist_name
         , a.the
    order 
        by c_ones desc limit 50
    the big difference will occur if some of those columns should be indexed but aren't, and you then index them

    r937.com | rudy.ca | 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)
    Thanks I'll try thats but looks like the way I had it when I had my problems i.e. ibf_charts as the main table the process was still running 25 mins later but think with what you've sorted I can see my mistake will let you know how it goes when i'm feeling brave again.

    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

  4. #4
    SitePoint Enthusiast Mickj's Avatar
    Join Date
    Jun 2002
    Location
    Margate, Kent, UK
    Posts
    52
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    p.s. All ID Fields are indexed, the site really needs good indexing otherwise it will run like a dog with all thats on there and will be.

    You'll see what I Mean by looking at this object which has one Query driving the main display I would recomend a broadband connect otherwise it could take a few seconds to build it all.

    http://www.database-dreams.co.uk/Com...chr=1&site=181
    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

  5. #5
    SitePoint Enthusiast Mickj's Avatar
    Join Date
    Jun 2002
    Location
    Margate, Kent, UK
    Posts
    52
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks r937 Works A Treat And Did check the indexes and found one that wasn't right so sorted that and your sql works a treat.

    It's quetion 16 in the list the singles having the largest amount of data
    http://www.database-dreams.co.uk/Com...t=QAS&site=181

    You could say I'll be back Soooon

    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
  •