Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am currently aggregating posts from different social networks. Currently I have 50-100 million posts from Facebook, Twitter, Youtube, Instagram , Pinterest.

Consider a table posts

posts
{
    id  int(11),
    user_id  int(11),
    url varchar(256), 
    image varchar(256), 
    source int(11),  // Social Network Source 
    created bigint(20), // Publish time of the post 
    visible int(1) // Public or Private Posts
}

Using InnoDB

Indexes :

primary key on id
user_id,source
user_id,created

Space usage

Type    Usage 
Data    45,876.0    MiB
Index   4,959.0 MiB
Total   50,835.0    MiB

Using Amazon RDS with 7.5 GB RAM.

The queries I am executing are as follows

select id 
from posts 
where user_id={user_id} 
  and visible=1  
order by created desc 
  LIMIT 20 ;


select id 
from posts 
where user_id={user_id} 
  and source={your_network} 
  and visible=1 
order by created desc 
  LIMIT 20 ;

For users with > 500k posts sometimes my queries take a really long time.

I have run explain and you can see the results below

EXPLAIN SELECT id
FROM  `posts` 
WHERE user_id =123529745 and source=1 and visible=1
ORDER BY created DESC 
LIMIT 20

id    select_type       table   type    possible_keys   key     key_len ref     rows    Extra 
1       SIMPLE          posts   ref     user_id_2,user_id_4,user_id_3,created   created 4       const   954174  Using where; Using index

id    select_type       table   type    possible_keys   key     key_len ref     rows    Extra 
1       SIMPLE          posts   ref     user_id_2,user_id_4,user_id_3,created   created 4       const   742308  Using where;

Can you please suggest me any optimizations?

share|improve this question

migrated from stackoverflow.com Jun 20 '13 at 6:44

This question came from our site for professional and enthusiast programmers.

    
Can you explain your indexes a bit better. Looks to me like an index on just user_id would do the job. –  Tony Hopkinson Jun 19 '13 at 23:22
    
@TonyHopkinson How does an index on user_id optimize ORDER BY created? –  Barmar Jun 19 '13 at 23:25
    
Index on (user_id,source) filters posts by network source. Index on (user_id,created) is for order by optimization. –  Nikhil Jun 19 '13 at 23:26
    
@Barmar, I was thinking more along the lines of it using a user_id index in the where. Unless I misread the explain, it's using the created index and testing each one for user_id? –  Tony Hopkinson Jun 20 '13 at 20:51

1 Answer 1

This is your query:

SELECT id
FROM  `posts` 
WHERE user_id =123529745 and source=1 and visible=1
ORDER BY created DESC

You are doing only equality comparisons in the where and then sorting by created.

A single index on: posts(user_id, source, visible, created, id) should allow the entire query to be satisfied only by the index -- first finding the right rows, then sorting by them, and then fetching the id.

share|improve this answer
    
Will it also satisfy the query SELECT id FROM posts WHERE user_id =123529745 and visible=1 ORDER BY created DESC ? –  Nikhil Jun 20 '13 at 0:44
    
@Nikhil . . . No. MySQL has a pretty good description of how indexes get used (dev.mysql.com/doc/refman/5.5/en/multiple-column-indexes.html). MySQL might still satisfy all columns from the index, saving on the fetch from the original pages, which may be the root of the performance problem. –  Gordon Linoff Jun 20 '13 at 0:47

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.