CODEWITHSUNDEEP
mysqlsqldatabasejoomlajoomla3.0
Alexandru Vlas
Alexandru Vlas

Reputation: 1415

Database query taking forever to load

I have a query that I'm trying to create but it takes forever for the page to load when the query is active...

What I'm trying to do is:

I have a video website, and when you click on a video, you are redirected to the VIDEO page where you can see the video and the url structure is like this:

www.site.com/VIDEO_CATEGORY_NAME/VIDEO_ID.html

The video_category and video_id are in separate tables.

What I'm trying to achieve is: make a link so when user clicks it will get redirected to a RANDOM video page (random video).

Here is the query I'm trying to write:

$db = JFactory::getDBO();
$query = "SELECT distinct a.*,b.category FROM #__hdflv_upload a LEFT JOIN #__hdflv_category b on a.playlistid=b.id or a.playlistid=b.parent_id WHERE a.published='1' and b.published='1' and b.id=" . $videocategory1 . " and a.id != " .$videoid1 ." order by rand()";
$db->setQuery($query);
$result = $db->loadResult();

and the link:

<a href="/player/<?php echo $result; ?>.html">random video</a>

When the query is on the page it takes forever to load the page... Can somebody give me a hand please. Thank you

Upvotes: 0

Views: 345

Answers (1)

Kickstart
Kickstart

Reputation: 21533

To avoid the OR in the ON clause of the join, try using a UNION instead.

SELECT a.*, b.category 
FROM #__hdflv_upload a 
INNER JOIN #__hdflv_category b 
ON a.playlistid=b.id 
WHERE a.published='1' AND b.published='1' 
AND b.id=" . $videocategory1 . " 
AND a.id != " .$videoid1 ."  
UNION
SELECT a.*, b.category 
FROM #__hdflv_upload a 
INNER JOIN #__hdflv_category b 
ON a.playlistid=b.parent_id 
WHERE a.published='1' AND b.published='1' 
AND b.id=" . $videocategory1 . " 
AND a.id != " .$videoid1 ." 
ORDER BY RAND()

The UNION also removes duplicate records, so rendering the DISTINCT irrelevant.

Note that ORDER BY RAND() can be pretty inefficient. There are more efficient solutions but advising on that would require knowing more about your data (eg, table declares, if id fields are contiguous, etc).

Upvotes: 3

Related Questions