0

I am having some issues with a query that uses a GROUP BY method. The query seems to take quite a while to execute and sometimes chewing up all of the memory on the server. I checked the tmp directory and it has plenty of space, so I am unsure of where to look next.

Here is the query:

SELECT count(p.id) AS total, p.user_id, u.username 
FROM posts p
INNER JOIN users u
ON (p.user_id = u.id)
WHERE p.timeposted >= :time
AND p.user_id <> '2246'
GROUP BY p.user_id 
ORDER BY total DESC
LIMIT 5

Here is a look at my posts table:

CREATE TABLE `posts` (
  `id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `post_id` int(11) NOT NULL,
  `timeposted` int(11) NOT NULL COMMENT '11',
  `brand` varchar(150) NOT NULL,
  `postcontent` text NOT NULL,
  `category` int(11) NOT NULL,
  `album` int(11) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Indexes for table `posts`
--
ALTER TABLE `posts`
  ADD PRIMARY KEY (`id`),
  ADD KEY `user_id` (`user_id`),
  ADD KEY `category` (`category`),
  ADD KEY `post_id` (`post_id`),
  ADD KEY `album` (`album`)

Any thoughts on what may be causing this query to get choked up?

zeropsi
  • 682
  • 9
  • 24
  • How large are these tables? Note that selecting `u.username` precludes the use of an index for `GROUP BY`. You should also run `EXPLAIN` to see what the query is actually doing. – Tim Biegeleisen Jul 11 '17 at 02:43
  • @TimBiegeleisen: The posts table has 537107 records and the users table has 11086 records. – zeropsi Jul 11 '17 at 02:48
  • Here is the EXPLAIN results: `1 SIMPLE p range user_id user_id 4 426808 Using where; Using temporary; Using filesort 1 SIMPLE u eq_ref PRIMARY PRIMARY 4 vvazxa_awwssc.c.user_id 1` – zeropsi Jul 11 '17 at 02:49
  • Have a look [here](https://stackoverflow.com/questions/13633406/using-index-using-temporary-using-filesort-how-to-fix-this). It seems that you current plan is to be expected. – Tim Biegeleisen Jul 11 '17 at 02:55
  • @TimBiegeleisen thanks! Should I be looking for an alternative way to write the query, or a better way for the server to process the query? – zeropsi Jul 11 '17 at 03:06
  • Hi, Please see if the below query works. SELECT COUNT(p.id) AS total, p.user_id, u.username FROM users u INNER JOIN posts p ON (u.id = p.user_id) WHERE p.timeposted >= :TIME AND u.user_id <> '2246' GROUP BY u.user_id ORDER BY total DESC LIMIT 5 I have changed the join table and selected data from the table which has less records to optimize the query. – user3454116 Jul 11 '17 at 03:56
  • @user3454116 if I change the GROUP BY to the u.user_id versus p.user_id, then there is no results returned. If I keep it as p.user_id, then I have the same issues. – zeropsi Jul 11 '17 at 13:22
  • hi, Could you please describe the purpose of the two tables as i assume the users table is the master and it should have all your user details. – user3454116 Jul 11 '17 at 19:51
  • @user3454116 the posts table contains all of the posts from each of the users. The users table contains all of the user details. I am attempting to write a query that I can use to show the top posters per day / month / year / all-time. I just started with the per day and that's when the query was performing poorly. – zeropsi Jul 11 '17 at 21:30
  • hi @user1278584 this means that the join should work even if you use user id from user table. However what i see from your join is that your are joining user id with the post id. Do you have any reason for this, as i assume it should be joined with user id from post and user table – user3454116 Jul 13 '17 at 13:32
  • @user3454116 - `INNER JOIN users u ON (p.user_id = u.id)` I am joining the posts table with the users table user the user id field. – zeropsi Jul 13 '17 at 13:45
  • So in that case, just change the group by in my query to u.id and i think it should give you a result set. – user3454116 Jul 13 '17 at 19:27
  • @user3454116 - the query works, however, it still caused my MySQL to choke-up the server and die, forcing me to restart MySQL. I am guessing it has something to do with my tmp directory that the query is writing to. – zeropsi Jul 13 '17 at 19:46

0 Answers0