针对大型单表的MySQL查询优化[关闭]

Question

I need to ask you about making my MySQL query more efficient. I have a table of posts with 1M records.

I am not using any join, and it's fetching result from database in 5-10 seconds - I don't know how can I make it efficient to retrieve data fast. Here is my query:

SELECT
    post_id, post_title, post_des, post_date,
    post_status, user_id, post_price
FROM
    post

Here is the table structure:

CREATE TABLE `post` (
  `post_id` int(11) NOT NULL AUTO_INCREMENT,
  `post_title` varchar(255) NOT NULL,
  `post_des` text NOT NULL,
  `post_date` datetime NOT NULL,
  `edit_date` datetime NOT NULL,
  `post_status` varchar(255) NOT NULL,
  `user_id` int(11) NOT NULL,
  `featuered` varchar(255) NOT NULL,
  `sub_cat_id` int(11) NOT NULL,
  `cat_id` int(11) NOT NULL,
  `post_price` varchar(100) NOT NULL,
  `post_img` varchar(255) NOT NULL DEFAULT 'uploads/no_image.jpg',
  `post_country_id` int(11) NOT NULL,
  `post_state_id` int(11) NOT NULL,
  `post_city_id` int(11) NOT NULL,
  `post_ref` varchar(255) NOT NULL,
  PRIMARY KEY (`post_id`,`post_date`)
) ENGINE=InnoDB   DEFAULT CHARSET=latin1

Answer 1

You'll need to read this first : http://dev.mysql.com/doc/refman/5.0/en/mysql-indexes.html

And this : How do MySQL indexes work?