checcco checcco - 4 months ago 19
SQL Question

MySQL question: Indexes on columns!

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! :)

Answer

Yes, you definitely should add the index.

CREATE INDEX Post_author_id ON Posts(author_id);

As further evidence, run

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