I am having working web application based on PHP/MySQl where users can login and posts(similar to twitter).
I am using just 3 db tables named users, posts and follows.
I need some advice on how to get the MySQL to respond to a select query from "posts" table within 3 to 4 secs ("posts" have more than 1 million records).
Can any one advice on this please.
It sounds to me, like you either need to work on your indexing and what you're passing in to the "WHERE" clause or look at some form of data caching. I'm not a mysql expert so others might have more of an idea under the hood and I'm not sure what levels mysql starts to creak at. But you're asking quite an open ended question. What does the table definition look like and what indexes do you have currently? What does a typical query look like?