0

I need some advice. To be more general I explain on following example: I have MySQL InnoDB table 'posts' with fields:

`id` - INT UNSIGNED AUTOINCREMENT
`title` - VARCHAR(100)
`abstract` - TEXT #can be converted to VARCHAR(2048)
`content` - TEXT #text + thml tags
`category` - SMALLINT
`created_on` - DATETIME
`modified_on` - DATETIME
`enabled` - TINYINT(1)
`deleted` - TINYINT(1)

With inexes on

`id` - PRIMARY
`category` - INDEX
`modified_on` - INDEX
(`enabled`,`deleted`) - INDEX

table contain 10-15 million of records. I'm doing query

SELECT id, title, abstract FROM posts 
WHERE enabled = 1 AND deleted = 0 
    AND category IN ( {id's} )
ORDER BY modified_on DESC 
LIMIT {offset}, {limit}

With 50-100 concurrent requests to MySQL server it goes down. Each query executes in 0.1-0.5s, it depends on limit clause.

Do I need separate index for 'category' as sometimes I'm browsing for all posts from all categories or I can add 'category' to (enabled,deleted) index and pass id's of all categories (~200) every time I need posts from all of them?

What solutions can lead to improve speed of such query?

Thanks in advance.

G.N.
  • 11
  • 3
  • Take a look at EXPLAIN statement, it should help you to get more information, http://dev.mysql.com/doc/refman/5.5/en/using-explain.html. – pedromarce Mar 05 '13 at 12:55
  • How large are your `offset` and `limit`? – Quassnoi Mar 05 '13 at 13:03
  • limit is usually 10-100, and offset could be any from 0 to the 10-15 millions. This is pagination, it's cached, but after every change in posts `modified_on` changes to. Actually `modified_on` better to call `last_open_on`. So cache become invalid every time somebody view any post - this post should be first in pagination since then. – G.N. Mar 05 '13 at 13:12

1 Answers1

1

I think you should add category in index containing 'enabled' and 'deleted'.

Hoping it will reduce your query time

Muhammad Sohaib Ayub
  • 136
  • 1
  • 1
  • 12