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".

• palacsint: I think English table and column names would improve readability a lot for the majority of users here.
• DarkMantis: 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.
• Michel Ayres: I'll change the names to make things easy to read. sorry about that.
• Corbin: 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.)

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

• Michel Ayres: Really good idea about the GREATEST.The "imagem_destaque" is not possible (I GUESS) to get as a JOIN because there are tons of imgs there and sometimes none. So if I put it as an AND, this should remove some results (when the img are NULL). Right?
• Jared Kipe: I think the "imagem_detaque" will work in the JOIN, note that I moved the condition for that table to the whole WHERE clause, "AND lote_album.ordem = 1". I'm not sure what you mean by remove some results where the img are NULL. If you want to not have a row returned if the img is NULL you should add a clause to the WHERE like "AND imagem_destaque IS NOT NULL". This will lower the total amount of rows returned. If you mean that the JOIN will limit the rows returned to only where the img is not NULL, that is not the case because I used an outer join.
• Michel Ayres: The GREATEST didn't work. He brings a "lance(bid)" OR "NULL", even "lance_minimo(minimum_bid)" is not null
• Michel Ayres: But the image part worked really perfect. I'm taking yours as the right answer.