Results 1 to 5 of 5

Thread: random video fuction made me crazy

  1. #1
    Join Date
    Sep 2010
    Location
    Pokhara Nepal
    Posts
    49

    Thumbs down random video fuction made me crazy

    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.
    Last edited by hostonnet; 12-30-2010 at 08:53 AM.
    Nepal's Largest video community portal. share wisely at WWW.CLONEVIDEOS.COM

  2. #2
    Join Date
    Dec 2009
    Posts
    71

    Default

    Hi,

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

    Find and remove

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

    FIND

    PHP Code:
    $category_all = array(
        
    'random',
        
    'recent',
        
    'viewed',
        
    'discussed',
        
    'favorites',
        
    'rated',
        
    'featured'
    ); 
    REPLACE WITH

    PHP Code:
    $category_all = array(
        
    'recent',
        
    'viewed',
        
    'discussed',
        
    'favorites',
        
    'rated',
        
    'featured'
    ); 
    FIND AND REMOVE

    PHP Code:
    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

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

    Buyscripts Team

  3. #3
    Join Date
    Sep 2010
    Location
    Pokhara Nepal
    Posts
    49

    Default i got second message from my hosting, could you please help me?

    Quote Originally Posted by vshare View Post
    Hi,

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

    Find and remove

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

    FIND

    PHP Code:
    $category_all = array(
        
    'random',
        
    'recent',
        
    'viewed',
        
    'discussed',
        
    'favorites',
        
    'rated',
        
    'featured'
    ); 
    REPLACE WITH

    PHP Code:
    $category_all = array(
        
    'recent',
        
    'viewed',
        
    'discussed',
        
    'favorites',
        
    'rated',
        
    'featured'
    ); 
    FIND AND REMOVE

    PHP Code:
    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

    Code:
    <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
    Nepal's Largest video community portal. share wisely at WWW.CLONEVIDEOS.COM

  4. #4

    Default

    Hi,

    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

    BuyScripts Helpdesk

    with your vshare admin and FTP login details.

    Thanks

    Buyscripts Team

  5. #5
    Join Date
    Sep 2010
    Location
    Pokhara Nepal
    Posts
    49

    Default i did not fine the codes in my video.php file

    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.
    Nepal's Largest video community portal. share wisely at WWW.CLONEVIDEOS.COM

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
  •