ford.cotterill 0 Newbie Poster

Hi I have the two queries below, they run far too slow around the 20 second mark , I have around 18,000 rows on the videos table and 2,000 on the other. Any thoughts? Thanks

  $query="SELECT videos.VIDEOID, videos.title,
          (select video_thumb_chosen.thumb
           from video_thumb_chosen
           where video_thumb_chosen.videoid = videos.VIDEOID
           group by video_thumb_chosen.thumb
           order by count(video_thumb_chosen.thumb) desc limit 1
          ) as thumb,
          videos.rating, videos.runtime, videos.viewcount, videos.public, videos.time_added, videos.HD
    FROM videos
    where  videos.active='1' && videos.gytube=0  && videos.categories <> 7
    ORDER BY videos.last_viewed
    desc limit 10"; 
    $executequery = $conn->execute($query);
    $wvids = $executequery->getarray();
    STemplate::assign('wvids',$wvids);


id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   PRIMARY videos  ALL NULL    NULL    NULL    NULL    18234   Using where; Using filesort
2   DEPENDENT SUBQUERY  video_thumb_chosen  ALL NULL    NULL    NULL    NULL    3573    Using where; Using temporary; Using filesort


    $query = "SELECT videos.VIDEOID, videos.title,
        (select video_thumb_chosen.thumb
           from video_thumb_chosen
           where video_thumb_chosen.videoid = videos.VIDEOID 
           group by video_thumb_chosen.thumb
           order by count(video_thumb_chosen.thumb) desc limit 1
           ) as thumb, 
           videos.rating, videos.runtime, videos.viewcount, videos.public, videos.time_added, videos.HD 
           FROM videos 
           where videos.active='1' && videos.rdtube <> 0 && videos.gytube=0 && videos.rdtube <> 0 && videos.categories <> 7 group by videos.viewcount
           order by videos.viewcount desc limit 10";    
    $executequery = $conn->execute($query);
    $vcvids = $executequery->getarray();
    STemplate::assign('vcvids',$vcvids);



id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   PRIMARY videos  ALL NULL    NULL    NULL    NULL    18234   Using where; Using temporary; Using filesort
2   DEPENDENT SUBQUERY  video_thumb_chosen  ALL NULL    NULL    NULL    NULL    3487    Using where; Using temporary; Using filesort
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.