my site needs to store the ip and timestamp of every visit on mysql. i am concerned that very quickly i will have 1e6 rows in my database.
我的站点需要存储每次访问mysql的ip和时间戳。我担心我的数据库中很快就会有1e6行。
what is the best way to compress a date on mysql or java? does mysql already compress dates? ideally, I would like to un-compress the date values rather quickly to generate reports.
在mysql或java上压缩日期的最佳方式是什么?mysql是否已经压缩了日期?理想情况下，我希望不压缩日期值，而是快速生成报告。
Update: sorry i meant a mil per day. but I guess that is still minuscule.
更新:对不起，我说的是每天一英里。但我想这仍然是微不足道的。
3 个解决方案
#1
2
Mate, one million rows is a tiny database. I wouldn't be worrying too much about that. In any case, MySQL uses a pretty compressed format (3 bytes) anyway as per this page:
伙计，一百万行是一个很小的数据库。我不会太担心这个。在任何情况下，MySQL都会使用一个非常压缩的格式(3个字节)。
DATE: A three-byte integer packed as DD + MM×32 + YYYY×16×32
In other words, at bit level (based on the 1000-01-01 thru 9999-12-31 range):
换句话说，在位级(基于1000-01-01到9999-12-31范围):
00000yyy yyyyyyym mmmddddd
#2
1
Use the built in MySQL datetime type. A million rows isn't that many.
使用内置的MySQL datetime类型。一百万行并不是那么多。
#3
1
A mysql timestamp would be only 4 bytes. An integer representing the timestamp would be the same. It would be efficient to save it as a mysql type since you'd be able to index and/or query based on that column efficiently.
mysql时间戳只有4个字节。表示时间戳的整数将是相同的。将它保存为mysql类型会很有效，因为您可以根据该列高效地索引和/或查询。
Any "compressed" form not a mysql type would be inefficient to query.
任何“压缩”形式而非mysql类型的查询都是低效的。
#1
2
Mate, one million rows is a tiny database. I wouldn't be worrying too much about that. In any case, MySQL uses a pretty compressed format (3 bytes) anyway as per this page:
伙计，一百万行是一个很小的数据库。我不会太担心这个。在任何情况下，MySQL都会使用一个非常压缩的格式(3个字节)。
DATE: A three-byte integer packed as DD + MM×32 + YYYY×16×32
In other words, at bit level (based on the 1000-01-01 thru 9999-12-31 range):
换句话说，在位级(基于1000-01-01到9999-12-31范围):
00000yyy yyyyyyym mmmddddd
#2
1
Use the built in MySQL datetime type. A million rows isn't that many.
使用内置的MySQL datetime类型。一百万行并不是那么多。
#3
1
A mysql timestamp would be only 4 bytes. An integer representing the timestamp would be the same. It would be efficient to save it as a mysql type since you'd be able to index and/or query based on that column efficiently.
mysql时间戳只有4个字节。表示时间戳的整数将是相同的。将它保存为mysql类型会很有效，因为您可以根据该列高效地索引和/或查询。
Any "compressed" form not a mysql type would be inefficient to query.
任何“压缩”形式而非mysql类型的查询都是低效的。