I have the web application where i am serving file downloads in php. All is working fine and i have the download column in file field where i increment it.

But i want to have full statictics of downloads like how many downloads on each day or in month or time of day or from which location

But i am not able o find how to design the mysql tables for that. Do i need to lo every download request in database for that. Any design pattern or example of that in real world

  • Statistics are usually computed from server logs. They have exactly the data you need. You just need to process them before they get rotated to oblivion. – Jan Hudec Jul 30 '13 at 6:29

Ordinary, you'll save the date and time and location of each download, given that the table should be optimized for fast insertion, but retrieval may be slow.

Then, you may have an OLAP cube which, based on the original table, will store the number of downloads in a way appropriate for your criteria, i.e. per day or month or location.

You can start by modeling the table which is used to record download hits:

enter image description here

This gives you basic information about the download, i.e. that a download happened at a given date and time from a given geographical location. isProcessed is 0 by default; you'll see how is it used a bit later.

Given the lack of indexes on the table, insertion of data would be fast. Selection, on the other hand, can be really slow, but it doesn't matter: we wouldn't use this table for search.

The data from this table would be transformed into this one on schedule (at night, every hour, once per second, depending on your requirements). Once transferred, the entry is marked as processed by changing IsProcessed to 1.

enter image description here

This table contains indexes to be able to quickly find the information you need, for example count downloads per location like this:

select sum(CountDownloads) from ProcessedDownload where GeographicalLocation = :location`

It may be enough for your needs, or you can go further and do an OLAP cube with specific dimensions depending on the criteria you need.

enter image description here

If you want to go even further, search about snowflake schema. You can implement it for both date-time and location, given that depending on the context, the star schema (the current one) may be more appropriate.

As suggested in the comments, server logs may be another source for the ETL, replacing the database table. I imagine the pros would be:

  • No database to create in the first place; no connections to the database to manage, etc.

  • The previous point also means that you don't have to do anything with your web application to be able to report such statistics. Expanding statistics to other web applications is straightforward too, since you don't need to change them.

  • Server logs are expected to be very fast, very probably faster than the insertion of a row in a table in a database.

  • i am not advace db expert , i only know mysql with basic queries. how can i do that tsuff which u talk about like fast insertion and olap cube. i have no idea whta are those. any tutorials or example for that – user2424160 Jul 30 '13 at 6:32
  • 1
    "how can i do that tsuff which u talk about": by learning, as any other developer. A good starting point: en.wikipedia.org/wiki/OLAP_cube – Arseni Mourzenko Jul 30 '13 at 6:35
  • i have read that article and this as well mysqlperformanceblog.com/2010/07/12/intro-to-olap but still i didnt get how can i start implementing in my case. is there any link where i can see from start to finish how thats done – user2424160 Jul 30 '13 at 6:47
  • @MainMa I know it's been a while, but why link FactDownload to CountryDimension directly if you can always get the country info by joining through CityDimension? I recently started to believe that this actually is a bad approach, but I'm sure you had your reasons to do it like that. Not being sarcastic, I'm genuinely wondering. – aexl Oct 8 '15 at 10:19
  • @TheSexiestManinJamaica: the goal of an OLAP cube is specifically to get information as fast and simply as possible. If you don't have FactDownloadCountryDimension, you'll need an additional join, making information retrieval both slightly slower and more complicated. – Arseni Mourzenko Oct 8 '15 at 10:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.