John Davidson

Message:

I have some responsive images that are being outputted onto a page where the URLs come from filenames stored in a MySQL database. The images, although optimised (even the large versions are < 200KB), always take 5.00s to load.

The code is below, at this stage there are only 6 images in the database for testing, but I can't work out why this kills the page-load speed. When I remove the images or add in a static image taken from a site folder and type the path manually this issue doesn't happen.

The $filename is a taken from a URL parameter on the page and the details then fetched from the MySQL database with PHP.

The images are produced at 3 different sizes when uploaded with the Imagick PHP library (500,750 and 1000px) and the sizes added via string concatenation.

The problem may be being caused by the MySQL database itself on MAMP, but as well as looking into that I thought I'd better check I'm not doing something completely wrong here (I'm relatively new to PHP/MySQL and web development generally).

<?php



isset($_GET['filename']) ? $filename = $_GET['filename'] : header("Location: login.php");



$stmt = $connection->prepare("SELECT * FROM imageposts WHERE filename = :filename");



$stmt->execute([

':filename' => $filename

]); 



$www_root = "http://localhost:8888/site";



$db_image_filename = htmlspecialchars($row['filename']);

$db_image_ext = htmlspecialchars($row['file_extension']);

$db_image_title = htmlspecialchars($row['image_title']);



?>



// image output



<img

src="<?php echo $www_root . '/images-lib/' . $db_image_filename . '-500' . '.' . $db_ext; ?>"

srcset="<?php echo $www_root . '/images-lib/' . $db_image_filename . '-500' . '.' . $db_ext; ?> 500w,

        <?php echo $www_root . '/images-lib/' . $db_image_filename . '-750' . '.' . $db_ext; ?> 750w"

alt="<?php echo $db_image_title; ?>" 

title="<?php echo $db_image_title; ?>"

>

Note: a typical filename is 614b66956a121_1632331413-500 which is generated via the Imagick library when the image is uploaded, and $db_ext is either .jpeg or .png

Image below showing the 5 seconds for image load.

---

My Answer:
compressed and resized), are taking a long time to load when fetched from the database using PHP.

Here are some possible reasons for this issue and some solutions to improve the loading time of the images:

1. Check the image file size: Even if the images are optimised, they may still have a large file size. Make sure that the file size of the images is not too large, as this can slow down the loading time. You can use tools like ImageOptim or TinyPNG to further compress the images.

2. Use lazy loading: Implement lazy loading for the images so that they only load when they are in the viewport of the user. This can help improve the initial loading time of the page.

3. Use a content delivery network (CDN): Consider using a CDN to serve the images, as this can help reduce the load time by serving the images from servers that are closer to the user.

4. Cache the images: Implement caching for the images so that they are stored locally on the user's device after the initial load. This can help improve the loading time for subsequent visits to the page.

5. Optimize the database query: Make sure that the database query used to fetch the image URLs is optimized and efficient. Avoid fetching unnecessary data and consider using indexes to speed up the query.

6. Use asynchronous loading: Load the images asynchronously using JavaScript to prevent them from blocking the rendering of the rest of the page. This can help improve the overall loading time of the page.

By implementing these solutions, you should be able to improve the loading time of the optimised images fetched from the MySQL database via PHP.