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, 
    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), 
    ) 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".
  • \$\begingroup\$ I think English table and column names would improve readability a lot for the majority of users here. \$\endgroup\$ – palacsint Jun 21 '12 at 21:47
  • \$\begingroup\$ 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. \$\endgroup\$ – DarkMantis Jun 21 '12 at 21:55
  • \$\begingroup\$ I'll change the names to make things easy to read. sorry about that. \$\endgroup\$ – Michel Ayres Jun 22 '12 at 4:24
  • \$\begingroup\$ 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.) \$\endgroup\$ – 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, 
    (SELECT lance 
        FROM lance 
        WHERE lote.codigo = lance.codigo_lote 
        ORDER BY lance.data DESC 
        LIMIT 0,1), 
) AS lance2, 
-- 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
  • \$\begingroup\$ 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? \$\endgroup\$ – Michel Ayres Jun 22 '12 at 11:37
  • \$\begingroup\$ 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. \$\endgroup\$ – Jared Kipe Jun 22 '12 at 14:39
  • \$\begingroup\$ The GREATEST didn't work. He brings a "lance(bid)" OR "NULL", even "lance_minimo(minimum_bid)" is not null \$\endgroup\$ – Michel Ayres Jun 22 '12 at 19:05
  • \$\begingroup\$ But the image part worked really perfect. I'm taking yours as the right answer. \$\endgroup\$ – Michel Ayres Jun 23 '12 at 1:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.