many small vs one large query

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
Kieran Huggins
DevNet Master
Posts: 3635
Joined: Wed Dec 06, 2006 4:14 pm
Location: Toronto, Canada
Contact:

many small vs one large query

Post 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!
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post 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.
User avatar
Kieran Huggins
DevNet Master
Posts: 3635
Joined: Wed Dec 06, 2006 4:14 pm
Location: Toronto, Canada
Contact:

Post by Kieran Huggins »

thanks Weirdan - I'll give that a whirl :-)
User avatar
veridicus
Forum Commoner
Posts: 86
Joined: Fri Feb 23, 2007 9:16 am

Post 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.
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

Can you benchmark both ways to see which would perform better?
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Post by Benjamin »

Use a stored procedure if your version of mysql supports it.
User avatar
Kieran Huggins
DevNet Master
Posts: 3635
Joined: Wed Dec 06, 2006 4:14 pm
Location: Toronto, Canada
Contact:

Post 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 :-)
User avatar
onion2k
Jedi Mod
Posts: 5263
Joined: Tue Dec 21, 2004 5:03 pm
Location: usrlab.com

Post 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.
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post 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).
Post Reply