random video fuction made me crazy

[clonevideos]

Dear friends
i got a message from my hosting regarding random things and i don't know what is that mean? Please help me out or else my hosting is making me problem due to cpu usages issues.
here is the message they send me first:
1) Hello,
I'm writing you about your database clovidz. Please do not use "order by
rand()" on shared mysql, it is inefficient and must randomize the entire
table to return the 20 records you want. Further use of this, or of the
order by rand() causing problems, may result in your database being
disabled or the videos table disabled. You can write me about this issue
at
# Query_time: 3003.739149 Lock_time: 0.099743 Rows_sent: 0
Rows_examined: 49478
use clovidz;
SET timestamp=1292517650;
SELECT * FROM `videos` WHERE
`video_type`='public' AND
`video_active`='1' AND
`video_approve`='1'
ORDER BY rand()
LIMIT 14140, 20;
I asked them how to fix and they replied the second message like this:
2)Thanks for writing me back, and sorry for the delay. The issue is due to
the website code using "order by rand()", which is used to randomize the
records returned. It also must randomize the entire table when it runs,
even if you only ask for a few records. If you are not the person who
developed the software using this database, this will be more difficult.
If that is the case, you can check if there is a way to turn off the
"show random 20 videos" feature, that would likely stop this problem
query. If that is not possible, then besides having a person change the
website code to not run this, or make it more efficient, this will
eventually outgrow shared mysql hosting and will need a MySQL VPS or more
powerful mysql hosting at another host. As more video data is added to
that table, it will get less and less efficient and cause more impact on
mysql. Please let me know what can be done to fix the query.
Thanks!
Any fixes? please help me.

[vshare]

Hi,

If you want to remove random videos, please do the following steps

Find and remove

1.htaccess

RewriteRule ^random/(.*) video.php?category=random&page=$1

2.video.php

FIND

$category_all = array(
    'random',
    'recent',
    'viewed',
    'discussed',
    'favorites',
    'rated',
    'featured'
); 

REPLACE WITH

$category_all = array(
    'recent',
    'viewed',
    'discussed',
    'favorites',
    'rated',
    'featured'
); 

FIND AND REMOVE

else if ($category == 'random')
    {
        $view['html_title'] = 'Random Videos';
        $view['display_order'] = 'Random';
        $sql = "SELECT * FROM `videos` WHERE
               `video_type`='public' AND
               `video_active`='1' AND
               `video_approve`='1'
                $channel_sql
                $sql_adult_filter
                ORDER BY rand()
                LIMIT $start_from, $config[num_watch_videos]";
    } 

3.templates/menu_watch.tpl

FIND AND REMOVE

<li><a href="{$base_url}/{if $smarty.request.viewtype eq "detailed"}detailed/{/if}random/">Random</a></li>

Thanks

Buyscripts Team

[clonevideos]

Hi,

If you want to remove random videos, please do the following steps

Find and remove

1.htaccess

RewriteRule ^random/(.*) video.php?category=random&page=$1

2.video.php

FIND

$category_all = array(
    'random',
    'recent',
    'viewed',
    'discussed',
    'favorites',
    'rated',
    'featured'
); 

REPLACE WITH

$category_all = array(
    'recent',
    'viewed',
    'discussed',
    'favorites',
    'rated',
    'featured'
); 

FIND AND REMOVE

else if ($category == 'random')
    {
        $view['html_title'] = 'Random Videos';
        $view['display_order'] = 'Random';
        $sql = "SELECT * FROM `videos` WHERE
               `video_type`='public' AND
               `video_active`='1' AND
               `video_approve`='1'
                $channel_sql
                $sql_adult_filter
                ORDER BY rand()
                LIMIT $start_from, $config[num_watch_videos]";
    } 

3.templates/menu_watch.tpl

FIND AND REMOVE

<li><a href="{$base_url}/{if $smarty.request.viewtype eq "detailed"}detailed/{/if}random/">Random</a></li>

Thanks

Buyscripts Team

I got another message from my hosting and i want to ractify the situation could you please help me?
here is the message i got;
Hello,
I wrote you 1 month ago about your database "clovidz" and your use of
"order by rand()". I haven't head a response from you, and this morning
the mysql server had high load due to these queries still causing issues.
The below queries are taking 200 to 300+ seconds to run, processing
53000 records and must randomize a large amount of data to return the 20
records. Due to no response about the slow queries using more than your
fair share of the mysql server, I am moving your databases to a MySQL VPS
until this issue is fixed. You can write me at [email protected] when
this has been removed from your site, and I can move you back to shared
hosting. The first week of MySQL VPS is free, so if you can fix it
within the first week, no charges will be placed on your account.
# Query_time: 395.613946 Lock_time: 0.050090 Rows_sent: 20
Rows_examined: 53224
use clovidz;
SELECT * FROM `videos` WHERE
`video_type`='public' AND
`video_active`='1' AND
`video_approve`='1'
ORDER BY rand()
LIMIT 13900, 20;
# Query_time: 586.654760 Lock_time: 0.008073 Rows_sent: 20
Rows_examined: 49704
use clovidz;
SELECT * FROM `videos` WHERE
`video_type`='public' AND
`video_active`='1' AND
`video_approve`='1'
ORDER BY rand()
LIMIT 10380, 20;
# Query_time: 528.365574 Lock_time: 0.000146 Rows_sent: 20
Rows_examined: 55844
use clovidz;
SELECT * FROM `videos` WHERE
`video_type`='public' AND
`video_active`='1' AND
`video_approve`='1'
ORDER BY rand()
LIMIT 16520, 20;
Thanks!
Justin K

[vshare2]

Hi,

To solve issues by mysql rand() function, do the following.


Open video.php

find:

$sql = "SELECT * FROM `videos` WHERE
               `video_type`='public' AND
               `video_active`='1' AND
               `video_approve`='1'
                $channel_sql
                ORDER BY rand()
                LIMIT $start_from, $config[num_watch_videos]";

replace with:

$rand_start = rand($start_from, $view['total'] - $config['num_watch_videos']);
        $sql = "SELECT * FROM `videos` WHERE
               `video_type`='public' AND
               `video_active`='1' AND
               `video_approve`='1'
                $channel_sql
                LIMIT $rand_start, $config[num_watch_videos]";

If you can't do it, open a support ticket at

BuyScripts Helpdesk

with your vshare admin and FTP login details.

Thanks

Buyscripts Team

[clonevideos]

i did not find the code that you have mentioned in your earlier message.
you wrote:

To solve issues by mysql rand() function, do the following.


Open video.php

find:

Code:
$sql = "SELECT * FROM `videos` WHERE `video_type`='public' AND `video_active`='1' AND `video_approve`='1' $channel_sql ORDER BY rand() LIMIT $start_from, $config[num_watch_videos]";
replace with:

Code:
$rand_start = rand($start_from, $view['total'] - $config['num_watch_videos']); $sql = "SELECT * FROM `videos` WHERE `video_type`='public' AND `video_active`='1' AND `video_approve`='1' $channel_sql LIMIT $rand_start, $config[num_watch_videos]";
If you can't do it, open a support ticket at

could you please check. i have just created a support ticket for you with my login details.