Member Avatar for hwoarang69

this page just load random images from database.
problem is that this page take like 10sec to load. which is too slow. is there any thing i can do to load it faster. note i want store image in database.

<?php
include("include/header.php");

//check, if user is loged in or not
if(isset($_SESSION['username']))      /*** user is loged in ***/
{
    $user = $_SESSION['username'];    //get name of who is loged in

    $count = 0;
    $Q = "SELECT (SELECT image_short_name FROM user WHERE username = '$user') as `image_short_name`"
    .",`image_id`,`user_id`,`image_short_name`,`image`"
    ." FROM `image`"
    ." ORDER BY RAND()"
    ." LIMIT 30";
    $R = mysql_query($Q);

    echo "
        <head>
            <link rel='stylesheet' type='text/css' href='css/top_menu.css' />
            <link rel='stylesheet' type='text/css' href='css/all_css.css' />
        </head>

        <div id ='bg3'>
            <div id='context3'>

                <table  width='1000px' height='500px' cellpadding='45px'>                       
                    <tr height='100px'>     
                    ";           
                        while($row = mysql_fetch_assoc($R))
                            {
                                //get user_id from image table
                                $user_id_r         = $row['user_id'];
                                $image_short_name_r = $row['image_short_name'];

                                //get user name from user table
                                $queryget = mysql_query("SELECT username FROM user WHERE user_id = '$user_id_r'") ;
                                $row2 = mysql_fetch_assoc($queryget);   
                                $user_name_db = $row2['username'];

                                echo "<td width='100px'>";
                                $image_db = $row['image'];
                                $src = "data:image/gif;base64," . $image_db;
                                echo" <a href='zoom.php'><img src=\"$src\" width='130px' height='130px' class='image_p' /></a>";
                                echo "<center id='image_name'><a href='zoom.php'>" . $image_short_name_r . "</a></center>";
                                echo " <center id='image_name2'>by $user_name_db</center>";
                                echo "</td>";
                                $count++;
                                if($count == 5)       //5 rows
                                {
                                    echo "</tr><tr height='100px'>"; // Start a new row at 6 cells
                                    $count = 0;
                                }
                            }
                echo " 
                    </tr>
                </table>

            </div>
        </div>   
        ";
}
  • 2 Contributors
  • 1 Reply
  • 1K Views
  • 1 Hour Discussion Span
  • Latest Post Latest Post by Member #120589

Dani AI

Generated

a 10s wait here is mostly self‑inflicted: 30 images are inlined as base64, there is one SQL hit per image to get the username, and ORDER BY RAND() forces MySQL to sort the whole table. @diafol is right about file size; there is another kicker: base64 makes the payload ~33% larger and stops the browser from caching across pages. Keep the images in MySQL if you must, but serve them as real URLs and send tiny thumbnails.

Example pattern (single query + thumb endpoint + lazy load):

// page.php
$stmt = $pdo->query("
  SELECT i.image_id, i.image_short_name, u.username
  FROM image i
  JOIN user u ON u.user_id = i.user_id
  ORDER BY i.image_id DESC
  LIMIT 12
");
foreach ($stmt as $row) {
  echo '<img loading="lazy" width="130" height="130" ' .
       'src="image.php?id='.(int)$row['image_id'].'&t=thumb" ' .
       'alt="'.htmlspecialchars($row['image_short_name']).'">';
}
// image.php (streams BLOB thumbnail with caching)
$id = (int)$_GET['id'];
$t  = $_GET['t'] === 'thumb' ? 'thumb' : 'full';
$q  = $pdo->prepare("SELECT {$t}_blob AS b, {$t}_mime AS m, {$t}_etag AS e FROM image WHERE image_id=?");
$q->execute([$id]); $r = $q->fetch();
header("Content-Type: {$r['m']}");
header('Cache-Control: public, max-age=31536000, immutable');
header('ETag: "'.$r['e'].'"');
if (isset($_SERVER['HTTP_IF_NONE_MATCH']) && trim($_SERVER['HTTP_IF_NONE_MATCH']) === '"'.$r['e'].'"') { http_response_code(304); exit; }
echo $r['b'];

Quick wins:

  • Generate and store a 130x130 thumbnail BLOB at upload (GD/Imagick). Aim for ~10–30 KB.
  • Replace per-row lookup with the join above; index image.user_id and user.user_id.
  • Ditch ORDER BY RAND() on big tables. Precompute a rand_key column (indexed) once and SELECT ... WHERE rand_key BETWEEN x AND x+delta LIMIT 12, or page/infinite‑scroll.
  • Stop base64 in HTML; store raw binary in BLOBs.
  • Switch mysql_* to PDO/mysqli with prepared statements.
Member Avatar for Member #120589

Do you really need to use a nested SQL query? A join will be quicker.
Also, your images may be enormous, even though your display size is small. If each image is 1Mb+ in size, then 30 images will kill you. Your host will probably try to contact you as well! Profile images need to be VERY small. Perhaps using an ajax scroller or paginator can help so that you only display, say 10 at a time?

If your members are uploading their own photos, either have a filesize limit (say 50kB)
My DW Avatar: 5.7K (GIF), 15K (JPEG), 13K PNG32 (less for PNG16, PNG8) for a 100x100px image.

You may get better results from limiting filetype too. For oversize (pixels or memory), you can decide whether to reject the image or convert it (e.g. GD library).

Just a few thoughts.

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.