Inner select performance

Question

I have the following code, but I don't know how to make it run faster.

SELECT lot.order as lot_order, 
    lot.title as lot_title, 
    lot.id as lot_id, 
    lot.id_auction, 
    IF ( 
        (SELECT bid 
            FROM bid 
            WHERE lot.id = bid.id_lot 
            ORDER BY bid.date DESC 
            LIMIT 0,1) > lot.bid_minimum, 
        (SELECT bid 
            FROM bid 
            WHERE lot.id = bid.id_lot 
            ORDER BY bid.date DESC 
            LIMIT 0,1), 
        lot.bid_minimum 
    ) AS bid, 
    youtube, 
    (SELECT path_image 
        FROM lot_album 
        WHERE lot_album.id_lot = lot.id 
        AND order = 1) as image_featured
FROM lot
WHERE lot.excluded <> 1
AND lot.id_auction = 42
ORDER BY lot.order ASC

As you can see, I have the IF to check if the value of the select is > bid.minimum. The only way to improve this is check it on the programming language (PHP/.NET...) or there is another way?

---

• "lot_album" is a table where you can have lots of images, but also none. But only one image per lot will have order 1 so that image will be the "image_featured".

Answer 1

This should be a little better. I believe joins are faster than nested selects, and that if with nested select may or may not select twice or use cached response. I can't think of a good way of getting rid of the one nested select. Otherwise, maybe you could write it as a stored procedure or virtual table. (Note* any spelling mistakes of table or column names are a result of autocorrect)

SELECT lote.ordem as lote_ordem, 
lote.titulo as lote_titulo, 
lote.codigo as lote_codigo, 
lote.codigo_leilao, 
GREATEST( 
    (SELECT lance 
        FROM lance 
        WHERE lote.codigo = lance.codigo_lote 
        ORDER BY lance.data DESC 
        LIMIT 0,1), 
    lote.lance_minimo 
) AS lance2, 
youtube, 
youtube, 
-- lance.codigo_cadastro, 
lote_album.caminho_imagem as imagem_destaque, 
FROM lote
LEFT JOIN lote_album
ON lote_album.codigo_lote = lote.codigo 
WHERE lote.excluido <> 1
AND lote.codigo_leilao = 42 
AND lote_album.ordem = 1
ORDER BY lote.ordem ASC