Minimizing MySQL queries for forum thread index idea
Moderator: General Moderators
- JAB Creations
- DevNet Resident
- Posts: 2341
- Joined: Thu Jan 13, 2005 6:44 pm
- Location: Sarasota Florida
- Contact:
Minimizing MySQL queries for forum thread index idea
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?
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?
- Peter Anselmo
- Forum Commoner
- Posts: 58
- Joined: Wed Feb 27, 2008 7:22 pm
Re: Minimizing MySQL queries for forum thread index idea
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.
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.
- JAB Creations
- DevNet Resident
- Posts: 2341
- Joined: Thu Jan 13, 2005 6:44 pm
- Location: Sarasota Florida
- Contact:
Re: Minimizing MySQL queries for forum thread index idea
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!
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!
- Peter Anselmo
- Forum Commoner
- Posts: 58
- Joined: Wed Feb 27, 2008 7:22 pm
Re: Minimizing MySQL queries for forum thread index idea
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.
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.
- JAB Creations
- DevNet Resident
- Posts: 2341
- Joined: Thu Jan 13, 2005 6:44 pm
- Location: Sarasota Florida
- Contact:
Re: Minimizing MySQL queries for forum thread index idea
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.
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.
- Chris Corbyn
- Breakbeat Nuttzer
- Posts: 13098
- Joined: Wed Mar 24, 2004 7:57 am
- Location: Melbourne, Australia
Re: Minimizing MySQL queries for forum thread index idea
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.
Sometimes 4 simple queries are better than 1 complex query.
- Chris Corbyn
- Breakbeat Nuttzer
- Posts: 13098
- Joined: Wed Mar 24, 2004 7:57 am
- Location: Melbourne, Australia
Re: Minimizing MySQL queries for forum thread index idea
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).
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).
- JAB Creations
- DevNet Resident
- Posts: 2341
- Joined: Thu Jan 13, 2005 6:44 pm
- Location: Sarasota Florida
- Contact:
Re: Minimizing MySQL queries for forum thread index idea
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?
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
Post your query and we'll try to helpAlso when I attempted to do two separate LEFT JOIN's my query broke...so I'm not sure about that either?
- JAB Creations
- DevNet Resident
- Posts: 2341
- Joined: Thu Jan 13, 2005 6:44 pm
- Location: Sarasota Florida
- Contact:
Re: Minimizing MySQL queries for forum thread index idea
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...
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...
...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...
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...
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'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'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_idI'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?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!
)
Re: Minimizing MySQL queries for forum thread index idea
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:
Write:
You can use aliases to write shorter queries, by substituting the alias with the full table name:
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:
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.
Code: Select all
SELECT user_username, user_username_base, ...Code: Select all
SELECT user_accounts.user_username, user_accounts.user_username_base, ...Code: Select all
SELECT ua.user_username, ua.user_username_base, ...
FROM user_accounts AS ua ...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'- JAB Creations
- DevNet Resident
- Posts: 2341
- Joined: Thu Jan 13, 2005 6:44 pm
- Location: Sarasota Florida
- Contact:
Re: Minimizing MySQL queries for forum thread index idea
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...
The print_r() for this is as follows...
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'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?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!
)
Re: Minimizing MySQL queries for forum thread index idea
Certainly, you can use aliases for column names as well. For example, if you write:
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.
Code: Select all
SELECT ua.user_username AS last_reply_username ...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.
- JAB Creations
- DevNet Resident
- Posts: 2341
- Joined: Thu Jan 13, 2005 6:44 pm
- Location: Sarasota Florida
- Contact:
Re: Minimizing MySQL queries for forum thread index idea
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...
...and print_r() outputs...
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);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
Use var_dump() instead. Works for many more cases as well