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