CODEWITHSUNDEEP
phpmysqlsql
Lucas
Lucas

Reputation: 159

Which way is more efficient to store stats on mysql?

I have an statistics database for my website visitors and i need to find which is the most efficient way to store the data:

I came across to 2 options.

Option Number 1:

+-----------------------------+
+ date        | country       +
+-----------------------------+
+  2014-03-20 | US            +
+  2014-03-20 | US            +
+  2014-03-20 | NL            +                 
+  2014-03-21 | US            +
+-----------------------------+

And then get the visitors data with Select count(*) from table

Option number 2:

+-----------------------------+
+ date        | country | visits+
+-----------------------------+
+  2014-03-20 |     US | 2    +
+  2014-03-20 |     NL | 1    +
+  2014-03-21 |     US | 1    +
+-----------------------------+

And then get the visitors data with Select sum(visits) from table

Which do you think is the best option? or is there a better option yet? Please have in mind this in case it has a huge amount of traffic.

Thank you very much in advance.

Upvotes: 0

Views: 392

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269663

Obviously, the more efficient way would be to use the second option with the count. Multiple rows are rolled up into a single row, just for the expense of a 4-byte integer. In general, having a table where multiple the rows are identical is inefficient.

So, for querying purposes the second choice is going to be better.

In terms of creating and maintaining the table, the first choice is more efficient. Inserting a new record is a less expensive operation than updating an existing row. This probably isn't a big deal, but there might be situations where this make a difference.

Upvotes: 1

Related Questions