StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POSQL count many-to-many values or have it counted every time new row is added?
primarykey
Id
2148005
data
AcceptedAnswerId
2148122
AnswerCount
5
ClosedDate
CommentCount
2
CommunityOwnedDate
CreationDate
2010-01-27T15:16:43.580
FavoriteCount
0
LastActivityDate
2010-01-27T16:18:18.493
LastEditDate
2010-01-27T16:09:50.527
LastEditorUserId
239241
OwnerUserId
239241
ParentId
0
PostTypeId
1
Score
3
ViewCount
1487
LastEditorDisplayName
text
Body
I am using MySQL (MyISAM) 5.0.41 and I have this query: <pre><code>SELECT `x`.`items`.id, `x`.`items`.name, COUNT(*) AS count FROM `x`.`items` INNER JOIN `x`.`user_items` ON `x`.`items`.id = `x`.`user_items`.item_id GROUP BY name HAVING count > 2 ORDER BY count DESC </code></pre> I have about 36,000 users, 175,000 user_items and 60,000 items which are constantly added to. So this query is getting a bit slow... Is it better to: <ul> <li>Have a <code>count</code> field in <code>items</code> and update that periodically (say each time a user adds an item)</li> <li>or run the query like this (slowly).. </li> </ul> Or is there any SQL that will populate the count field for me? Thanks
Tags
<sql><mysql><count><many-to-many>
Title
SQL count many-to-many values or have it counted every time new row is added?
singulars
PostAcceptedAnswerId
1. PO
 singulars
 PostTypePostTypeId
 PTAnswer
PostParentId
1. This table or related slice is empty.
PostTypePostTypeId
1. PTQuestion
UserLastEditorUserId
1. USbetamax
UserOwnerUserId
1. USbetamax
plurals
PostLinksPostIdRelatedPostId
1. This table or related slice is empty.
PostLinksRelatedPostIdPostId
1. This table or related slice is empty.
PostsAcceptedAnswerId
1. This table or related slice is empty.
PostsParentIdCreationDate
1. PO
 singulars
 PostTypePostTypeId
 PTAnswer
2. PO
 singulars
 PostTypePostTypeId
 PTAnswer
3. PO
 singulars
 PostTypePostTypeId
 PTAnswer
VotesPostIdCreationDate
1. VO
 singulars
 PostPostId
 POSQL count many-to-many values or have it counted every time new row is added?
 UserUserId
 This table or related slice is empty.
 VoteTypeVoteTypeId
 VTUpMod
2. VO
 singulars
 PostPostId
 POSQL count many-to-many values or have it counted every time new row is added?
 UserUserId
 This table or related slice is empty.
 VoteTypeVoteTypeId
 VTUpMod
3. VO
 singulars
 PostPostId
 POSQL count many-to-many values or have it counted every time new row is added?
 UserUserId
 This table or related slice is empty.
 VoteTypeVoteTypeId
 VTUpMod
CommentsPostId
1. CODatabase System? Version? What Indexes do you have in place?
 singulars
 PostPostId
 POSQL count many-to-many values or have it counted every time new row is added?
 UserUserId
 USKyle Butt
2. COEdited to show DB system and version.
 singulars
 PostPostId
 POSQL count many-to-many values or have it counted every time new row is added?
 UserUserId
 USbetamax

Querying!

Guidance

A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.