I've developed a URL shortener system like Bitly with same features , so the system also tracks clickers info and represent as graphs to the person who has shorten the link as analytics data. currently I'm using MySQL and have a table to store click info with this schema:
visit_id (int) ip (int) date (datetime) country browser device os referrer (varchar) url_id (int) //as foreign key to the shortened URL
and for now , just the
url_id field has index
The system should represent click analytics in the time periods the user wants, for example past hour, past 24 hours , the past month , ...
for example to generate graphs for past month , I do following queries:
SELECT all DAY(date) AS period, COUNT( * ) FROM ( SELECT * FROM visits WHERE url_id = '$url_id' ) AS URL WHERE DATE > DATE_SUB( CURRENT_TIMESTAMP( ) , INTERVAL 1 MONTH ) GROUP BY DAY( DATE ) //another query to display clicker browsers in this period //another query to display clicker countries in this period // ...
- for a shortened link with about 500,000 clicks , it takes about 3-4 seconds to calculate just the first query , so for total queries about 10-12 seconds which is terrible.
- lots of memory and CPU is needed to run such queries
1- how to improve and optimize the structure , so the analytics of high
traffic links will be shown in less than 1 second(like bitly and similar web apps) and with less usage
of CPU and RAM ? should I make an index on the fields
referrer ? if yes , how to do
that for the field
date because I should group clicks some times by
DAY(date), sometimes by
HOUR(date), sometimes by
2- is MySQL suitable for this application? assume at maximum my application should handle 100 million links and 10 billion clicks on them totally. Should I consider switching to an NoSQL solution for example?
3- if MySQL is ok , is my database design and table structure proper and well designed for my application needs? or you have better recommendations and suggestions?
UPDATE: I made an index on column
referrer but it didn't help at all and also damaged the performance and I think that's because of the low cardinality of this column (also others) and the big resulting index size related to the RAM of my server.
I think making index on these columns would not help to solve my problem, my idea is about one of these:
1- if using MySQL, maybe generating statistics using background processing for high traffic links is better instead of calculating lively at the user request.
2- using some caching solution like memcached to help MySQL with high traffic links.
3- using a NoSQL such as MongoDB and solutions like Map-Reduce which I am poorly familiar with and haven't used ever.
what do you think?