Clever Geek Handbook

MySQL question: indexes on columns!

I have a MySQL question

I have two tables (posts and authors) in a one to many relationship (since every 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 have 700,000 posts and 60,000 authors.

If I select an author (e.g. author_id = 45) and I want a random post to write him, I write:

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

I know this is correct, but when I got 4000 concurrent people on the internet it takes about 6 seconds ..

Maybe indexing the author_id column in the Posts table can speed things up?

Thanks everyone! :)

+2


source to share


6 answers


Yes, you should definitely add an index.

CREATE INDEX Post_author_id ON Posts(author_id);


As additional evidence, do

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


source


Indexing should reflect the most popular WHERE scenarios.

In this particular case, create your index and then change your query to this:

SELECT id,author_id,body 
FROM Posts 
WHERE author_id = 45 
ORDER BY RAND() 
LIMIT 1;


This will prevent searching for the schema before searching, thereby improving performance.

SELECT * is evil for high frequency queries.

+5


source


If you don't have a pointer to author_id either, be sure to put one on it. Also I'm not sure if ORDER BY RAND () is not responsible for the lack of performance. Try adding an index and it should already improve significantly.

0


source


Especially in a situation where you are reading your data much more than you are updating your data, be generous when setting up your indexing. Everything you ever had in a where clause must be indexed.

0


source


An index [possibly grouped] on Author_id will help definitively.

An additional risk factor appears in the ORDER BY RAND () part. Essentially this clause forces SQL to dynamically assign a random number to each row (for a given Author_id) and order them. This can become a bottleneck as some prolific authors start to have hundreds and thousands of posts.

0


source


if author_id is a foreign key then it doesn't need to create an index. It has a built-in index.

0


source






More articles:


All Articles