MySQL slow to check if rows exist in large table

Question

Currently I check to see if a user has viewed a video yet or not by checking if that user has recorded a view in our views table.

The query looks like this:

SELECT EXISTS(SELECT 1 FROM views WHERE videoid=:vid AND userid=:uid)

When displaying a page with video thumbnails, we overlayed "Seen", if the user has already watched this video, similar to the functionality on youtube.

The problem is that this query is rather slow to compute, as the views table has a large number of rows - 40 or 50ms. If a page has, say 60 video thumbnails displayed, there are 3 full seconds of SQL delay before the page can be rendered.

Is my problem architectural, or can I do something to speed the execution of the query up?

Does the views table have an index that covers videoid and userid? — Joni
– Joni, Commented Feb 28, 2016 at 9:34
CREATE TABLE views ( id int(11) NOT NULL AUTO_INCREMENT, videoid int(11) NOT NULL, ip varchar(32) NOT NULL, userid int(11) NOT NULL, when datetime NOT NULL, PRIMARY KEY (id) ) — aliask
– aliask, Commented Feb 28, 2016 at 9:40
When you generate the page of thumb nails, do you get those videos also from the database? In that case you should in the same SELECT statement get the views information joined with it. — trincot
– trincot, Commented Feb 28, 2016 at 9:45

Joni · Accepted Answer · 2016-02-28 09:47:42Z

1

You don't have an index on views that covers the videoid and userid columns, so the database is likely doing a full table scan. Adding the index should help. For example:

alter table views add index (videoid, userid);

Post the execution plan for your query if it's still slow.

answered Feb 28, 2016 at 9:47

Joni

112k14 gold badges151 silver badges201 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MySQL slow to check if rows exist in large table

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related