Page 1 of 1
many small vs one large query
Posted: Mon May 14, 2007 4:11 pm
by Kieran Huggins
a "best practice" question:
I have a table with several normal columns and one larger BLOB of between 1-30 kb (avg~=8k). I need to return about 200-ish rows from the DB, but I only need about 10 of the BLOBs.
Should I make it one query to get everything, including the BLOBs I don't need? Or should I build one query to get everything except the BLOBs and then another query for each of the BLOBs I want?
I know this kinda smells, but I can't think of a better way to do it.
Thanks guys!
Posted: Mon May 14, 2007 4:48 pm
by Weirdan
Should I make it one query to get everything, including the BLOBs I don't need? Or should I build one query to get everything except the BLOBs and then another query for each of the BLOBs I want?
I'd go with second route. Or you could select NULL as your blob field for those rows you don't need.
Posted: Tue May 15, 2007 1:41 am
by Kieran Huggins
thanks Weirdan - I'll give that a whirl

Posted: Wed May 16, 2007 9:47 am
by veridicus
I agree. In this case I'd go with the multiple queries. We have this discussion a lot at my office (one large versus many small queries) and it has to be considered on a case-by-case basis.
Posted: Wed May 16, 2007 4:18 pm
by RobertGonzalez
Can you benchmark both ways to see which would perform better?
Posted: Wed May 16, 2007 5:14 pm
by Benjamin
Use a stored procedure if your version of mysql supports it.
Posted: Thu May 17, 2007 3:25 am
by Kieran Huggins
I've never benchmarked SQL before but I've always wanted to try. In this case however it was more of a design decision.
I don't want to maintain 2 codebases on this project, but as soon as I have some appropriate sample data in the DB I'll do some quick and dirty prototyping and post the results!
Will check out the stored procedures as well

Posted: Thu May 17, 2007 8:05 am
by onion2k
If possible I'd use an IF in the SQL..
Code: Select all
SELECT `myTable`.`title`, `myTable`.`other_field`,
IF (`myTable`.`id` IN (1,2,3,4,5,6,7,8,9,10), `myTable`.`blob_column`, '') as `blob_column`
FROM `myTable`
WHERE 1
LIMIT 200
Clearly it'd depend on whether or not your condition for wanting the BLOB can be crowbarred into an IF in the SQL though.
Posted: Thu May 17, 2007 4:35 pm
by Weirdan
Clearly it'd depend on whether or not your condition for wanting the BLOB can be crowbarred into an IF in the SQL though.
That's what I meant by suggesting NULLs (instead of empty string in your example).