0

I am saving my users visits statistics like this in mySQL database :

  • visit_id
  • visit_page
  • visit_route
  • visit_referrer
  • visit_ip_address
  • visit_device
  • created_at
  • updated_at

my question is will my mySQL database handle this amount of visits like 100,0000 per day? and can it make problems in 5 years being saved like this?(100000 per day)

and if the answers are YES, so what is the optimized way to do this(I don't want to empty my visits table)

Hadi Khezrpor
  • 401
  • 5
  • 21
  • Do you actually want to keep your visits data for 5 years? also is the date of each visit is important or just the latest one? – MEDZ Dec 20 '19 at 08:45
  • I think that MySQL is not proper storage for that kind of data. Consider using, for example, ElasticSearch or some other NoSQL storage. – Krzysztof Szala Dec 20 '19 at 08:47
  • @medz yes maybe :D, yes because we want exact statics of our visits – Hadi Khezrpor Dec 20 '19 at 08:50
  • @krzysztof-szala unfortunately I don't know anything about no-sql in laravel... – Hadi Khezrpor Dec 20 '19 at 08:57
  • @HadiKhezrpor It doesn't matter what kind of framework you are using. You can simply use any PHP library via the composer to work with the NoSQL database. There is also a built-in MongoDB adapter in Laravel. Check it please, a relational database is one of the worst solution when you want to store your user login information. – Krzysztof Szala Dec 20 '19 at 09:23

1 Answers1

2

First of all, you may need to rethink MySQL. If you want to keep staying in RDBs you may want to research postgres, and with some optimization, it can handle the number of rows. On the other hand if you open to switch to NoSQL, then i recommend elastic search. It can handle this kind of data very well.

If you chose to stay with postgres/mysql. Then you can restructure your schema by separating the visitor data (unique users) from the visited_pages data as follows:

visitors


 - id
 - ip_address
 - device
 - first_visit (created_at)
 - latest_visit (updated_at)

visited_pages

 - id
 - page_title
 - page_route
 - first_visit (created_at)
 - latest_visit (updated_at)

page_visit

 - id
 - visitor_id
 - page_id
 - visited_at (created_at) //no need for updated at

The largest table will be the last one and it won't contain much data. and you will not have to use the same data like route, page title, ip address every time.

MEDZ
  • 2,227
  • 2
  • 14
  • 18