1 Vote Vote

MySQL question: Indexes on columns!

Posted by topdog 284 days ago Questions| author mysql posts All

Hi guys... I've a MySQL question

I've two tables (posts and authors) in a one to many relationship (since each post is written by an author and an author can write multiple posts).

So here are the tables:

Authors:
   id:BIGINT, name:VARCHAR(255)

Posts: 
   id:BIGINT, author_id:BIGINT, body:TEXT

I've got 700,000 posts and 60,000 authors.

If I choose an author (e.g. author_id = 45) and I want a random post written by him I write:

SELECT * FROM Posts WHERE author_id = 45 ORDER BY RAND() LIMIT 1;

I know this is right, but when I got 4,000 simultaneous people online it takes about 6 secs..

Maybe indexing author_id column in Posts table would speed up things?

Thank you all! :)

Discuss Bury


Who Voted for this Question