Fastest possible MYSQL SELECT query with JOINS (join itself)

Question

i am having some trouble with indices and the performance of my query. I am currently doing as following:

SELECT 
    images.imageId, 
    path, 
    fileName, 
    alt, 
    description, 
    width, 
    height 
FROM 
    images 
LEFT JOIN imagesEn ON images.imageId=imagesEn.imageId 

WHERE 

images.adId = ?

AND images.imageStatus = 1 
AND images.isPreview = 1 
AND (images.isBanner = 0 OR images.isBanner IS NULL) 

ORDER BY RAND() LIMIT 1;';

This gives me the image i want. But i am using responsive picture elements, there are smaller versions of the image. My limited sql-knowledge forced me to use another query then like so:

SELECT 
    images.imageId, 
    relativePathToImageFromImgFolder, 
    fileName, 
    altAttribute, 
    description, 
    width, 
    height 
FROM 
    images 
LEFT JOIN imagesEn ON images.imageId=imagesEn.imageId 

WHERE 

images.adId = ? 

AND images.imageStatus = 1 
AND images.isPreview = 0 
AND images.isSmallerVersionOf = ?

ORDER by width DESC

Then i am ending up with two queries and two arrays which gives me the main image plus all the smaller versions of the image to create my picture element. The join is needed because i have several languages (imagesEn, imagesDe, imagesFr, etc.) which joins the filename, attributes in the given language. I have just set single-column indices, multi-column indices are new to me i have heard from the the first time today :D. I am showing something like producte on a product page and my page-generation time is about 0.3 seconds (uncashed). All my other pages have 0.0x second which is my goal for that page, too. Plus i am doing this query inside a loop

$query = 'SELECT name, id, url, whatever from products where productColor = "blue";';
foreach($arr as $r)
{
    $str .= '<div>';
            $str .= $r['name'] . ' ' . $r['whatever'];
            $str .= self::createPictureElementFromId($r['id']);  //this calls the above queries to create the picture element with its main image and the child images (smaller versions for smaller screens)
    $str .= '</div>';
}

return $str;

Hope the description is precise enough ;). Thanks in advance

Answer 1

Try with this. It joins two times the images table (one for the normal image and the second for the smaller version if it exists), and two times the ImageEn table which should have the translation of the description and other things.

If you're only looking for one image your DB time won't be so much better.

The alias of the tables in the select fields should be wrong, because I don't know where it's each field. The description should be on imagesEn and imagesEn2

This only gets one smaller version of the image. If you have lots of smaller images you can join three times the images table, but IMHO it should be cleaner the way you're doing it at the moment in your code with two selects.

SELECT 
    i.imageId, 
    i.path, 
    i.fileName, 
    i.alt, 
    i.description, 
    i.width, 
    i.height,
    i2.relativePathToImageFromImgFolder, 
    i2.fileName, 
    i2.altAttribute, 
    i2.description, 
    i2.width, 
    i2.height

FROM 
    images as i
LEFT JOIN imagesEn as iEn ON i.imageId=iEn.imageId 
LEFT JOIN images as i2 on ((i2.isSmallerVersionOf = i.imageID) AND (i2.imageStatus = 1)  AND (i2.isPreview = 0))
LEFT JOIN imagesEn2 as IEn2 ON i2.imageId = iEn2.imageId

WHERE 

i2.adId = ?

AND i.imageStatus = 1 
AND i.isPreview = 1 
AND (i.isBanner = 0 OR i.isBanner IS NULL) 

ORDER BY RAND() LIMIT 1;