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