I suspect this might be one of those things that is going to be situation dependant, but I'd thought I poll the experience here and see what advice I can get.

I'm using Apache::DBI, mod_perl, Mysql, and all that sort of goodness with my site. Let's say that I have two tables (out of several I'm working with) that deal with forums on my site. One table holds a forum ID #, the base node of that forum via it's message ID, and several other specific details on the forum. The other table holds the messageid, the poster, and other details specific to the message, including the forum ID as above for the forum that the message is part of. One cgi script that I am writing will poll all the posts from a given poster, and then return details of those posts to the user, including information about which forum they were in, etc. Now easily, TISMTOWTDI, but the two obvious ones are:

• Perform one SQL query to get each message id; then foreach on that messageid to do another SQL to get the forum information, so that if the user has n messages, it would take n+1 SQL queries.
• Perform a single SQL query using the JOIN functionality that merges the tables on the forumid, and get all the information with 1 SQL query.

Obviously, the second seems to be reasonable, but when I try such queries at the mysql command line, they seem to take much longer than if I did the n+1 method, but this could just be an apparent effect. The SQL commands are mainly retrieval in this case, with the odd DATE_TO_UNIXTIME conversion of selected data, but no large amount of processing. Because I'm using Apache::DBI and mod_perl, I have a persistence DB connection, so I don't have to worry about start up costs. The SQL queries can be cache'd out prior to running anything, so I don't have to consider those as repeated operations.

Does anyone have any good practical experience in such a situation? Again, I suspect it's a matter of trying it out for myself, and either way, it's not a problem programming-wise.

---

Dr. Michael K. Neylon - mneylon-pm@masemware.com || "You've left the lens cap of your mind on again, Pinky" - The Brain

---

---

Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!

Titles consisting of a single word are discouraged, and in most cases are disallowed outright.

Read Where should I post X? if you're not absolutely sure you're posting in the right place.

Please read these before you post! —

• How do I compose an effective node title?
• How do I post a question effectively?
• Markup in the Monastery

Posts may use any of the Perl Monks Approved HTML tags:

a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr

You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)

	For:		Use:
	&		&
	<		&lt;
	>		&gt;
	[		&#91;
	]		&#93;

Link using PerlMonks shortcuts! What shortcuts can I use for linking?

See Writeup Formatting Tips and other pages linked from there for more info.