many small vs one large query
Moderator: General Moderators
- Kieran Huggins
- DevNet Master
- Posts: 3635
- Joined: Wed Dec 06, 2006 4:14 pm
- Location: Toronto, Canada
- Contact:
many small vs one large query
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!
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!
- Kieran Huggins
- DevNet Master
- Posts: 3635
- Joined: Wed Dec 06, 2006 4:14 pm
- Location: Toronto, Canada
- Contact:
- RobertGonzalez
- Site Administrator
- Posts: 14293
- Joined: Tue Sep 09, 2003 6:04 pm
- Location: Fremont, CA, USA
- Kieran Huggins
- DevNet Master
- Posts: 3635
- Joined: Wed Dec 06, 2006 4:14 pm
- Location: Toronto, Canada
- Contact:
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
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
If possible I'd use an IF in the SQL..
Clearly it'd depend on whether or not your condition for wanting the BLOB can be crowbarred into an IF in the SQL though.
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