Page 1 of 2
Minimizing MySQL queries for forum thread index idea
Posted: Sat Dec 06, 2008 12:03 am
by JAB Creations
I started creating my own forum software and I'm now working on both concept and code for forum thread index where the list of threads is displayed. My primary goal is to minimize the number of MySQL queries PHP makes and I'm curious as to what others think of my concept for approaching the issue.
At the moment at least I there are two tables involved, the user table and the thread table. While slightly redundant I've decided instead of making a ton of queries to simply statically store certain bits of information about each thread (such as the post count instead of having MySQL run circles to filter the whole database again just to determine that...per each thread listed). So any way I'm thinking of doing two queries...one to fetch the threads for the forum and the other to fetch the user names. I could statically store the user names however I want the retain the ability to have user's rename their account at some point so I view this as important as keeping it relational and their ID is stored in the table instead of a static name.
Any way I'm thinking if I store the thread and user names in arrays and then use the arrays I won't have to make a query in any loop...so I'll be able to create an array of ID's and then make a single MySQL query for the user names...and then output the XHTML code. This seems to make more sense to me though I'm not entirely sure how such arrays weigh against MySQL queries? Is this generally a good approach? Are there improvements to the concept that I could implement?
Re: Minimizing MySQL queries for forum thread index idea
Posted: Sat Dec 06, 2008 12:22 am
by Peter Anselmo
I've written a forum application before, and for the problems like you mentioned, I found making good use of SQL JOINS to be key to reducing the query count. Why query the the tables individually and make arrays representing the primary and foreign key relationships when you can let MySQL do the match for you?
Also, I assume you're only talking about the two tables relevant to the index page - because as far as the forum goes - you're definately going to need a "posts" table.
Re: Minimizing MySQL queries for forum thread index idea
Posted: Sat Dec 06, 2008 12:59 am
by JAB Creations
Ah! I forgot about joins! Eventually this stuff will all come naturally to me, I hope soon before I end up driving everyone here nuts.
I do have a table for posts though I'm working from the forum index to greater detail...so the second page is the thread list for each forum. After I have that working I'll move on to the individual thread page where posts are displayed and so forth.
Do you have a link to the forums you built that I can check out please? I'm developing everything locally though I also update a preview on my site that you're welcomed to check out here...
http://www.jabcreations.com/members/forums.php
Again, thanks for your suggestion!
Re: Minimizing MySQL queries for forum thread index idea
Posted: Sat Dec 06, 2008 1:20 am
by Peter Anselmo
Jab,
As far as joins go, there are several types (inner & outer), Make sure you consider both, although inner are much more commonly used.
Alas, the forum I built is no longer online. It never actually saw the light of production, it was a personal app I built to learn ColdFusion. When I was getting into web development, I built more things than I knew what to do with.
Your forum looks like it's really far along, keep up the good work. Let me know if you want any other opinions.
Re: Minimizing MySQL queries for forum thread index idea
Posted: Sat Dec 06, 2008 1:31 am
by JAB Creations
Thanks, it's only been a couple days that I've really worked on it but I'm very enthusiastic to replace all the third party modules on my site with my own! Plus it's a great learning experience!
I have looked numerous times and I wish I could find a good explanation about the differences between the various MySQL JOIN commands? I have a couple pages open from the w3schools site though I'm not sure when you'd want to use one over the others really...what sort of things they are best applied for. I used inner and left joins on the blog I wrote though it's still a bit vague to me.
Re: Minimizing MySQL queries for forum thread index idea
Posted: Sat Dec 06, 2008 7:56 am
by Chris Corbyn
Seriously, don't optimize early. Reads are cheap. It's writes that will slow you down.
Sometimes 4 simple queries are better than 1 complex query.
Re: Minimizing MySQL queries for forum thread index idea
Posted: Sat Dec 06, 2008 8:02 am
by Chris Corbyn
To put in simple terms what the joins are, you'll mostly choose between INNER or LEFT.
INNER JOIN requires the correct rows be present in BOTH tables involved in the join. If the correct rows are not present in either table then that row will not be selected, as if the record doesn't even exist.
LEFT JOIN only requires that the correct row be present in the table on the left hand side of the join. If the right had side of the join can't be matched, then all the fields for it will be the default values (such as NULL or "" or 0).
Re: Minimizing MySQL queries for forum thread index idea
Posted: Sat Dec 06, 2008 8:17 am
by JAB Creations
Thanks for the clarification, I was reading something along those lines on the w3schools website.
However if I can get this to work I'll be able to do a single SELECT query to fetch all the forum's threads...otherwise I think I'd have to construct an array of the last-post id's and then do a second MySQL query or something along those lines.
As I have been reading this morning I discovered that MySQL's AS command stands for alias...I think? It's like looking up concatenation without ever having heard of the word concatenation in a sense. What is the dot called? What is the AS called? That sort of ordeal...so can I combine MySQL AS alias command with a left join?
Also when I attempted to do two separate LEFT JOIN's my query broke...so I'm not sure about that either?
Re: Minimizing MySQL queries for forum thread index idea
Posted: Sat Dec 06, 2008 8:29 am
by Eran
Also when I attempted to do two separate LEFT JOIN's my query broke...so I'm not sure about that either?
Post your query and we'll try to help
Re: Minimizing MySQL queries for forum thread index idea
Posted: Sat Dec 06, 2008 9:15 am
by JAB Creations
My threads got merged I think? Sorry if I'm making a mess, these concepts are like oily soap!
This works and is sort of the basis...
Code: Select all
SELECT user_username, user_username_base, forum_thread_id, forum_thread_count_posts, forum_thread_count_replies, forum_thread_count_views, forum_thread_name FROM forum_threads LEFT JOIN user_accounts ON forum_thread_author_id = user_id WHERE forum_thread_forum_id='1'
The
forum_thread_author_id contains the thread's author's id while the
forum_thread_author_id_last contains the thread's last post's author's id.
First I tried doing two separate LEFT JOIN's...
Code: Select all
SELECT user_username, user_username_base, forum_thread_id, forum_thread_count_posts, forum_thread_count_replies, forum_thread_count_views, forum_thread_name FROM forum_threads LEFT JOIN user_accounts ON forum_thread_author_id = user_id LEFT JOIN user_accounts ON forum_thread_author_id_last = user_id WHERE forum_thread_forum_id='1'
...however PHP tells me it's not a valid MySQL statement...then I tried things such as using a comma between
forum_thread_author_id and
forum_thread_author_id_last.
I can sort of understand how I may be confusing MySQL...I'm trying to create two relations to the same column for two separate instances in the same query.
Then I tried something like this...
Code: Select all
LEFT JOIN user_accounts ON forum_thread_author_id AND forum_thread_author_id_last = user_id
That query returns only the last poster's username without an actual key (AS or alias? which I'm not sure how to set even though I tried emulating an earlier thread), the thread's author name becomes the latest poster's name. Here is a print_r example of the output...
Array
(
[0] => webmaster
[user_username] => webmaster
[1] => webmaster
[user_username_base] => webmaster
[2] => 1
[forum_thread_id] => 1
[3] => 1
[forum_thread_count_posts] => 1
[4] => 0
[forum_thread_count_replies] => 0
[5] => 12
[forum_thread_count_views] => 12
[6] => First Test Thread!
[forum_thread_name] => First Test Thread!
)
I've done some LEFT JOIN alias searches though I don't feel I have found anything along the lines of what I'm doing. Even if I get one alias to work (to generate a proper key/value set in the array) I'm not sure about trying to get data from the same ID for two separate instances. Also based on what Chris said and what I read on the w3schools site I'm pretty sure I want LEFT JOIN as I always expect data to exist when I have MySQL retrieve it from the tables. Thoughts please?
Re: Minimizing MySQL queries for forum thread index idea
Posted: Sat Dec 06, 2008 10:11 am
by Eran
When you start writing queries that involve multiple queries, specifying the table names of columns is not only good practice - it's a must. For example, instead of:
Code: Select all
SELECT user_username, user_username_base, ...
Write:
Code: Select all
SELECT user_accounts.user_username, user_accounts.user_username_base, ...
You can use aliases to write shorter queries, by substituting the alias with the full table name:
Code: Select all
SELECT ua.user_username, ua.user_username_base, ...
FROM user_accounts AS ua ...
You should also specify the tables in the ON clauses in case of ambiguity.
If you want to join twice to the same table, you must use different aliases for the joins. In you case the query should change to something like:
Code: Select all
SELECT ua_last.user_username, ua.user_username_base, ft.forum_thread_id, ft.forum_thread_count_posts, ft.forum_thread_count_replies, ft.forum_thread_count_views, ft.forum_thread_name
FROM forum_threads AS ft
LEFT JOIN user_accounts AS ua ON ft.forum_thread_author_id = ua.user_id
LEFT JOIN user_accounts AS ua_last ON ft.forum_thread_author_id_last = ua_last.user_id
WHERE ft.forum_thread_forum_id='1'
I wasn't sure about the user_username and user_username_base (which belongs to the original poster) but you can easily modify it to fit your logic.
Re: Minimizing MySQL queries for forum thread index idea
Posted: Sun Dec 07, 2008 6:28 am
by JAB Creations
AWESOME! AWESOME! AWESOME!
Thanks pytrin, after fixing another bug with MySQL (it was still display "user_accounts" table as "public_accounts") I spent the past two or three hours toying around with the code you provided. I think the important thing was that you provided the example within the context I am thinking about all of this in which greatly helps me understand what you're saying. While I can work with these results easily enough I'd like to see if we can take it to the last step but first where I have gotten thanks to your post...
Code: Select all
SELECT ua.user_username, ua.user_username_base, ua_last.user_username, ua_last.user_username_base, ft.forum_thread_id, ft.forum_thread_count_posts, ft.forum_thread_count_replies, ft.forum_thread_count_views, ft.forum_thread_name FROM forum_threads AS ft LEFT JOIN user_accounts AS ua ON ft.forum_thread_author_id = ua.user_id LEFT JOIN user_accounts AS ua_last ON ft.forum_thread_author_id_last = ua_last.user_id WHERE ft.forum_thread_forum_id='1'
The print_r() for this is as follows...
Array
(
[0] => JAB Creations
[user_username] => webmaster
[1] => jabcreations
[user_username_base] => webmaster
[2] => webmaster
[3] => webmaster
[4] => 1
[forum_thread_id] => 1
[5] => 1
[forum_thread_count_posts] => 1
[6] => 0
[forum_thread_count_replies] => 0
[7] => 12
[forum_thread_count_views] => 12
[8] => First Test Thread!
[forum_thread_name] => First Test Thread!
)
Again I
can work with this though it's not exactly friendly so my question now is can I somehow manipulate how MySQL returns the array in regards to the names of the keys? For example I'd rather refer to the thread author as
$row3['author_user_username'] instead of
$row3[0] and the last reply user name as
$row3['last_reply_user_username'] instead of
$row3['user_username']. In general what should I look at to change the array key output so I have something friendlier to work with in PHP?
Re: Minimizing MySQL queries for forum thread index idea
Posted: Sun Dec 07, 2008 6:53 am
by Eran
Certainly, you can use aliases for column names as well. For example, if you write:
Code: Select all
SELECT ua.user_username AS last_reply_username ...
Then 'last_reply_username' will be the associative index in the returned resultset.
I have to ask something - why are you showing both associative and numeric indexes in your query results? I use only one approach (usually associative) to work with resultsets.
Re: Minimizing MySQL queries for forum thread index idea
Posted: Sun Dec 07, 2008 7:13 am
by JAB Creations
ROCK ON!
Thanks a million! I hope others find this thread to be as useful in the future as I have.
I'm not sure why
print_r is showing associative and numeric indexes though I'm definitely up to following up on this to get it sorted out. I'm simply doing the following...
Code: Select all
SELECT ua.user_username AS author_username, ua.user_username_base AS author_username_base, ua_last.user_username AS last_username, ua_last.user_username_base AS last_username_base, ft.forum_thread_id, ft.forum_thread_count_posts, ft.forum_thread_count_replies, ft.forum_thread_count_views, ft.forum_thread_name FROM forum_threads AS ft LEFT JOIN user_accounts AS ua ON ft.forum_thread_author_id = ua.user_id LEFT JOIN user_accounts AS ua_last ON ft.forum_thread_author_id_last = ua_last.user_id WHERE ft.forum_thread_forum_id='1'
Code: Select all
$row5 = mysql_fetch_array($result5);
print_r($row5);
...and print_r() outputs...
Array
(
[0] => JAB Creations
[author_username] => JAB Creations
[1] => jabcreations
[author_username_base] => jabcreations
[2] => webmaster
[last_username] => webmaster
[3] => webmaster
[last_username_base] => webmaster
[4] => 1
[forum_thread_id] => 1
[5] => 1
[forum_thread_count_posts] => 1
[6] => 0
[forum_thread_count_replies] => 0
[7] => 12
[forum_thread_count_views] => 12
[8] => First Test Thread!
[forum_thread_name] => First Test Thread!
)
Re: Minimizing MySQL queries for forum thread index idea
Posted: Sun Dec 07, 2008 7:55 am
by Eran
Use var_dump() instead. Works for many more cases as well