# Inner select performance

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,
(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".
• I think English table and column names would improve readability a lot for the majority of users here. – palacsint Jun 21 '12 at 21:47
• Always try and limit your queries as that will save a lot of time. As I'm not sure what you're retrieving etc then I'm not sure if it is appropriate here, but yeah Limit queries, try to minimize the amount of sub-queries you have as that always takes a lot of time. – DarkMantis Jun 21 '12 at 21:55
• I'll change the names to make things easy to read. sorry about that. – Michel Ayres Jun 22 '12 at 4:24
• You could potentially adjust stackoverflow.com/a/2111420/567864 to do away with the sub queries and only have to do an IF. Am not sure how that would compare to subquery performance though. (I suspect it would be better with careful indexing.) – Corbin Jun 22 '12 at 7:54

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,