MySQL query optimization for large single table

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

we'll need more info than that.. Can you post your table structure and indexes? — user20232359723568423357842364, May 10 '13 at 19:32
You can't speed it up if you want to fetch *all* records at once. Do you? If not, what kind of data do you want to fetch? — Explosion Pills, May 10 '13 at 19:33
Please check the table structure. I am using it on classified website and it must fetch all records but by pagination php pagination using .... but when i see olx and other site there and millions of record and these websites are fast .... — M Shahzad Akram, May 10 '13 at 19:37
@MShahzadAkram - what indices have you set up on that table? What data are you retrieving? All of it? Subsets? Every column? — andrewsi, May 10 '13 at 19:42
@MShahzadAkram - how are you querying for records? You need to figure out what criteria your SELECT statements are using, and add indices as appropriate. — andrewsi, May 10 '13 at 19:46
You talked about "pagination"? Ever tried [LIMIT](http://dev.mysql.com/doc/refman/5.0/en/select.html)? — CodeZombie, May 10 '13 at 19:47
I am using php pagination with all records and i am select some of fields from table..... like title , description and date , price .... not fetching all columns .... on which column i can add index ? — M Shahzad Akram, May 10 '13 at 19:50
@MShahzadAkram - I can't answer that without seeing your SELECT statements. If you're selecting on `post_date`, then add an index on it. If you're selecting on `post_title`, add an index on it. — andrewsi, May 10 '13 at 19:54
SELECT `post_id`, `post_title`, `post_des`, `post_date`, `post_status`, `user_id`, `post_price`, FROM `post` — M Shahzad Akram, May 10 '13 at 19:58
also please if need to add indexes , mentioned me if there is any alter query to add indexes on the existing table ? — M Shahzad Akram, May 10 '13 at 20:01
for pagination you should be doing two queries one that gets the total count `select count(id) as post_count from post` and then using an limit clause in your query to get only the the records you need. @ZombieHunters comment links to the docs for how to use limit. — Orangepill, May 10 '13 at 20:03
@MShahzadAkram - is that your real query? So you're querying your table with 1,000,000 rows and printing all of them out, in no particular order, every time? — andrewsi, May 10 '13 at 20:04
@ZombieHunter yes you are right i am using this query and passing to a pagination class who will count and then set limit of 0,16 and return query and then i will use that further to fetch record to display ..... — M Shahzad Akram, May 10 '13 at 20:08
@andrewsi yes i am using this query ... but fetching required record from 10,000,00 total... — M Shahzad Akram, May 10 '13 at 20:08
@MShahzad: try adding an `ORDER BY`, `OFFSET` and `LIMIT` clauses to your statement: what difference do they make? — halfer, May 10 '13 at 20:41

score 1 · Accepted Answer · edited May 23 '17 at 11:49

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?

edited May 23 '17 at 11:49

Community

1
1

answered May 10 '13 at 19:33

Maxime D

51
3

Basically i am not using any join to other tables .... i have primary key in this table and i don't think there is need to add more indexes. please guide me if there is needed. thanks – M Shahzad Akram May 10 '13 at 19:43
Indexes can still improve query performance even if there are no other joins. @MaximeD, please add more information to your answer; link-only answers are frowned upon. – Chris Forrence May 10 '13 at 19:55

MySQL query optimization for large single table

1 Answers1