a) I've 1000000 domains
b) Every domain has about 100000 sites
c) each site has about 10000 visits daily / (5000 unique visits daily)
d) As who owns all individuals websites, I wish to see, the number of site visitors on selected sites I'd inside a selected amounts of time, for instance:
The number of unique customer were from fourth December 1987 to 23 April 2010 on mydomain.com/lessons
The number of unique customer were from 30 August 1996 to 16 This summer 2009 on yourdomain.com/reference ?
For any traditional SQL database this can be a discomfort.
What's the wisest approach ? What storage engine to make use of ?
I've only SQL understanding. Any extra assets greatly appreciated.
Using the amounts and potential queries much like individuals you have listed I'd greatly dobut that the simple SQL (PSQL/TSQL) database would suit your needs. Rather you will need some type of OLAP processing like SSAS (SQL Server Analysis Services) or perhaps an similar offering from Oracle.
I believe DB is the greatest method for this method You just need to cr4eate a few tables and spread the information together, e.g.:
Table: Domains [id, name] Table: Sites [id, domain_id, name] Table: Visits [id, site_id, date]
so that you can choose let say:
SELECT COUNT(v.id) FROM Visits AS v RIGHT JOIN Sites AS s ON v.site_id = s.id RIGHT JOIN Domains AS d ON s.domain_id = d.id WHERE d.name = 'mydomain.com' AND s.name = 'tutorials' AND v.date BETWEEN startDate AND endDate
startDate and endDate ought to be passed through programming language(PHP, ASP) or they may be set by hand within the Choose
Hope that can help.