I'm trying to build a aggregated reader website of multiple forums.
Because most queries are likely to be within same time periods for
I'm thinking about taking advantage of clustered index ordered by
But because it is not unique, i'm thinking about making primary key with unique id like:
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
I think I should've written more about use case scenarios:
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.
SELECT .. FROM written_time >= datetime_1weeksago
Users can see articles of every board for specific time periods
SELECT .. FROM written_time >= datetime1 AND written_time < datetime2
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.
SELECT .. FROM written_time >= '2012-07-01' AND written_time < '2012-08-01' .. keyword search using full-text engine
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
Background crawler works fetches large number of articles in two ways and appends in two directions:
repeatly updates recent articles (appends entries with written_time)
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)