I am doing a sub-query join to another table as I wanted to be able to sort the results I got back with it, I only need the first row but I need them ordered in a certain way so I would get the lowest id.
I tried adding
LIMIT 1 to this but then the full query returned 0 results; so now it has no limit and in the
EXPLAIN I have two rows showing they are using the full 10k+ rows of the
I wrote it this way to avoid having to query the
auction_media table for each row separately, but now I'm thinking that this way isn't that great if it has to use the whole auction_media table?
Which way is better? The way I have it or querying the
auction_media table separately? ...or is there a better way!?
Here is the code:
SELECT a.auction_id, a.name, media.media_url FROM auctions AS a LEFT JOIN users AS u ON u.user_id=a.owner_id INNER JOIN ( SELECT media_id,media_url,auction_id FROM auction_media WHERE media_type=1 AND upload_in_progress=0 ORDER BY media_id ASC ) AS media ON a.auction_id=media.auction_id WHERE a.hpfeat=1 AND a.active=1 AND a.approved=1 AND a.closed=0 AND a.creation_in_progress=0 AND a.deleted=0 AND (a.list_in='auction' OR u.shop_active='1') GROUP BY a.auction_id;
Edit: Through my testing, using the above query seems like it would be the much faster method overall; however I worry if that will still be the case when the
auction_media table grows to like 1M rows or something.