|
|
|
|
Thread title: A column that counts the number of index entries on a specific index? |
|
|
|
|
|
Thread tools
Search this thread
Display Modes
|
|
06-07-2012, 01:12 PM
|
#1
|
Status: Request a custom title
Join date: Feb 2005
Location:
Expertise:
Software:
Posts: 3,164
|
A column that counts the number of index entries on a specific index?
Bare with me as I try to explain.
I have two tables: Topics and Comments. topic.id is indexed to comment.topicid, for every comment that is indexed to topic.id I want to +1 a column in my comments table, called comments.replies.
I can do this incredibly easy with PHP, however, I'm left wondering if it's at all possible with just MySQL?
Here's my relationship diagram for better understanding: http://i.imgur.com/1nICb.png
|
|
06-07-2012, 03:02 PM
|
#2
|
Status: Geek
Join date: Apr 2006
Location: Denver, CO
Expertise: Software
Software: Chrome, Notepad++
Posts: 6,894
|
Select all the topics you want, left join all the relevant comments, group them by topic ID and make one of your rows a count of that ID.
Code:
SELECT
t.id.
t.author,
... (additional info you want from the topic table here)
c.forumtopicid,
COUNT(t.id) as reply_count
FROM
forum_topics t
LEFT JOIN
forum_comments c ON c.forumtopicid=t.id
GROUP BY
t.id
WHERE
t.category=1
If this query does not make sense see what it looks like without the GROUP BY statement and it might help.
|
|
06-07-2012, 04:13 PM
|
#3
|
Status: Request a custom title
Join date: Feb 2005
Location:
Expertise:
Software:
Posts: 3,164
|
Is this a better method than a trigger?
|
|
06-07-2012, 04:24 PM
|
#4
|
Status: Geek
Join date: Apr 2006
Location: Denver, CO
Expertise: Software
Software: Chrome, Notepad++
Posts: 6,894
|
A trigger? I'm not familiar with the term.
|
|
06-07-2012, 04:31 PM
|
#5
|
Status: Request a custom title
Join date: Feb 2005
Location:
Expertise:
Software:
Posts: 3,164
|
|
|
06-07-2012, 05:06 PM
|
#6
|
Status: Geek
Join date: Apr 2006
Location: Denver, CO
Expertise: Software
Software: Chrome, Notepad++
Posts: 6,894
|
Right. Those are bad for this because they require too much maintenance. You'll have to have one when posts are added, deleted, or moved. Furthermore if the trigger should for some reason fail you'll have to recompile all your data to make it valid again. Preforming counts on the spot requires no maintenance and has less ways to fail.
|
|
06-13-2012, 09:06 PM
|
#7
|
Status: Request a custom title
Join date: Feb 2005
Location:
Expertise:
Software:
Posts: 3,164
|
I looked into doing it straight from the SQL and wouldn't this be a better method?
$db->query("UPDATE `forum topics` SET `replies` = replies +1 WHERE `id` = '103' LIMIT 1");
|
|
06-13-2012, 10:00 PM
|
#8
|
Status: Geek
Join date: Apr 2006
Location: Denver, CO
Expertise: Software
Software: Chrome, Notepad++
Posts: 6,894
|
It poses the exact same problems, the counts can get messed up and need to be reset. The only advantage to updating a count like that would be speed on large counts. The method you just gave would be better from a coding standpoint and easier to implement on shared servers but may take slightly longer. I doubt the time difference would ever really matter though. .
|
|
|
|
|
|
|
|
Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
|
|
|
|