Reputation: 4253
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
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
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