CODEWITHSUNDEEP
mysqlsql
RGS
RGS

Reputation: 4253

Why this simple SELECT is taking 6 seconds when user has many comments?

I have this select to show users some notifications when someone comments in one post. I noticed that users that has posts with many comments it can take 6 seconds +.

select 'comments' prefix, c.foto, c.data as data, c.user,
       concat(k.user, ' comments your post') as logs
from comments c  

inner join posts p on c.foto = p.id 
inner join cadastro k on c.user = k.id 

where p.user = 1 and c.user <> 1 and c.delete = 0
order by c.data desc
limit 5

enter image description here

I'd like to show users notifications, someone comments your post, to do so, I used inned join on posts (to know if the comment is from user '1') and inner join cadastro (to get user nick name - user who comments user 1 post).

checking on where if user is 1, c.user <> 1 (not show his own comments notifications) and c.delete (comment not deleted).

my tables:

`posts` (
  `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `user` int(11) UNSIGNED NOT NULL,
  `foto` varchar(400),
  `data` datetime NOT NULL,
  `delete` tinyint(1) NOT NULL DEFAULT '0',
  FOREIGN KEY (`user`) REFERENCES cadastro (`id`),
  PRIMARY KEY (`id`)
)

`comments` (
  `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `foto` int(11) UNSIGNED NOT NULL,
  `user` int(11) UNSIGNED NOT NULL,
  `texto` varchar(3000) NOT NULL,
  `data` datetime NOT NULL,
  `delete` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `foto_delete` (foto, `delete`),
  FOREIGN KEY (`foto`) REFERENCES posts (`id`) ON DELETE CASCADE
)

any ideas why it is taking so long when an user has about 200.000 comments? (if user has 1000, it is fast).

Upvotes: 1

Views: 57

Answers (1)

Tony
Tony

Reputation: 17667

Without indexes, to run your Query the engine usually scans all rows looking for the required values in the ON, WHERE, as well the ORDER BY clause.

A simple thing you can do is to create the indexes:

CREATE INDEX cadastro_id ON cadastro(id);
CREATE INDEX posts_id ON posts(id);

CREATE INDEX posts_user ON posts(user);

CREATE INDEX comments_foto ON comments(foto);
CREATE INDEX comments_user ON comments(user);
CREATE INDEX comments_delete ON comments(delete);

CREATE INDEX comments_data ON comments(data);

Measure the current time it takes, then apply these Indexes and measure again, and tell here.

See also:
https://dev.mysql.com/doc/refman/5.7/en/create-index.html
https://dev.mysql.com/doc/refman/5.7/en/order-by-optimization.html

Upvotes: 1

Related Questions