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'm trying to build a aggregated reader website of multiple forums.

Because most queries are likely to be within same time periods for written_time column, I'm thinking about taking advantage of clustered index ordered by written_time.

But because it is not unique, i'm thinking about making primary key with unique id like:

(written_time, site_id, board_id, article_id)

I guess it would require much bigger space for index and have performance problem, but am I right?

Is it a good approach to make cluster index like this if I would like to take advantage of query results which have proximity about written-time?

EDIT

I think I should've written more about use case scenarios:

  1. the mirroring website's main page shows recent aggregated articles from every board so I want to minimize number of indices and size of each index.

    e.g. SELECT .. FROM written_time >= datetime_1weeksago

  2. Users can see articles of every board for specific time periods

    e.g. SELECT .. FROM written_time >= datetime1 AND written_time < datetime2

  3. Users can see articles which contains specific keyword for specific time chunk (e.g 201207), user can narrow search criteria down into some selected sites, search traffic volume is not high, Going to use full-text engine, frequent search result is cached by keyword*time_chunk.

    e.g. SELECT .. FROM written_time >= '2012-07-01' AND written_time < '2012-08-01' .. keyword search using full-text engine

    e.g. SELECT .. FROM written_time >= '2012-07-01' AND written_time < '2012-08-01' AND site_id IN (1,3,5,7,9) .. keyword search using full-text engine

  4. Background crawler works fetches large number of articles in two ways and appends in two directions:

    1. repeatly updates recent articles (appends entries with written_time)

    2. crawls and archive old articles (appends entries with written_time)

    this is why i changed my mind to use cluster index (primary key) based on written_time, not on auto increment nor on board_id

huge amount of articles from number of highly-active forums(boards)

share|improve this question
add comment

Your Answer

 
discard

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

Browse other questions tagged or ask your own question.