CODEWITHSUNDEEP
mysqlperformanceweb-applicationsstatisticsscalability
S. Saad
S. Saad

Reputation: 333

Best structure for MySQL table to hold statistical data

I have a need for a solution that would allow me to track every single click (and the link clicked, and the date) in a web application (PHP5 / MySQL5.7). The simplest solution is obviously a simple table :

CREATE TABLE stats_data (
    id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    log_date DATETIME NOT NULL DEFAULT NOW(),
    link VARCHAR(512) NOT NULL
)

I'm not such how this scales up performance-wise, as the expected amount of clicks per day could well go above 10000.

  1. Is this a reliable solution, say, after 5 months of data stored ?
  2. What optimizations could make this solution perform better?
  3. If not, what would be a better solution approach for this ?

Upvotes: 0

Views: 695

Answers (3)

Cristian Gonçalves
Cristian Gonçalves

Reputation: 912

There's a couple of thinks you can do to ensure performance:

By partitioning data by date columns you can "separate" data by hour / day / week / month / year... whatever you want...

Example:

CREATE TABLE members (
firstname VARCHAR(25) NOT NULL,
lastname VARCHAR(25) NOT NULL,
username VARCHAR(16) NOT NULL,
email VARCHAR(35),
joined DATE NOT NULL
)
PARTITION BY RANGE( YEAR(joined) ) (
  PARTITION p0 VALUES LESS THAN (1960),
  PARTITION p1 VALUES LESS THAN (1970),
  PARTITION p2 VALUES LESS THAN (1980),
  PARTITION p3 VALUES LESS THAN (1990),
  PARTITION p4 VALUES LESS THAN MAXVALUE
)

Therefore, imagining that you separates data by week, when you search by a log with date equal to '2016-08-25', that record will be searched only on logs with dates between '2016-08-22' and '2016-08-28'.

I hope this can help you.

Upvotes: 0

Henrik Hjalmarsson
Henrik Hjalmarsson

Reputation: 529

I guess you could possible put the links in a separate table and have your table reference that as a foreign key. Should possible make it faster to for example check the number of clicks on a specific link.

Depending on how accurate you want the data you could also aggregate it into another table in maby a nightly running operation of some sort (scheduled sp should work). That way you can have a table where you for example can se how many times a link was clicked in a specific interval, a day or an hour or whatever suits your needs. I've used this approach at work where we store statistic data on web-service calls in an application with very heavy load and it has been working fine with no performance issues what so ever.

Upvotes: 1

Wax Cage
Wax Cage

Reputation: 788

Mostly it depends on your use-case. What queries do you want to run over this dataset?

I would definitely recommend some document oriented database (like Redis or MongoDb), but as I said, it depends how will you use your data.

If you want to stick to MySQL, I have some advice on how to make that solution more reliable.

  1. Dont save every click into database each time is clicked, but store it into cache (memcached for example) and once every hour save into MySQL
  2. Make own table for each month to not make searches in one large table. And backup that table each month.

Upvotes: 1

Related Questions