Re: No problem, just performance question (a little ot)

On Sun, 18 May 2003 22:41:57 +0200 "Ryan A" <ryan@jumac.com> wrote: > Hi, > I have a table with around 30 fields, but most of the fields have only the > values 1 or 0. You better have ENUM or SET datatype there. Hope this was already thought by you. > I want to add the fields counter,month,day to the end of it so that i can > know how many times a particular record is being viewed... two fields are more than enough - reference ID and a TIMESTAMP. let me expand on this further below. > I will have around 5000 records there on a pretty busy site and when someone > clicks the link to see that particular recored (for our example a record > with $cno=777) As David pointed out - 5000 hits daily is pretty few. BUT, you might want to study your log files to know the maximum amount of hits per second that appeared to your site at any time of its existence. This means, if 1000 hits were done in one only hour while other 4000 in 23 hours, you should be understanding your traffic as 24.000 (24 * 1000) hits daily max. It makes a big difference. > I would 1) run a "update" for the $counter,$day based on $month and then > 2)run a select to get all the data from the DB Get two tables. One for logging in the ID of your product/data viewed and the timestamp when it occured. Second one should be what you already have but well indexed for a faster fetch (SELECT). > my question is: if i get 5000 hits a day each fetching on an average of 10 > records...will this put a big strain PHP/MySql? Keep in mind that, mySQL cannot afford too many record in it. I had trouble with my tables (deadly crash and dataloss) once they were over a million hits. This is something you would get within just half of the year. And, your data would be huge there. Do you really really really want to store the times of every hit? Think about it - how often will you need to know that? Split them daily - like hits of today update only one record andtomorrow there will be another one. This will reduce your hits by 5000 times apprx while creating another SELECT to know where to update. Simply incrementing the hit count for a dataset would be much smarter. > I am on a shared server (cant afford a dedicated one). Another reason to follow my advice. > Do you think i can safely implempt this (as i have not done so as yet) or > will the performance degrade so badly that the sacrifice for statistics too > high? Performace will definitely decrease, you you might want to increment only at the page's end with an at-mark (@) before the calls to DB. This way, should anything fail your page would still load. Consider also the most ideal logic - logging your hits in a simple flat file and then build your stats once in a while. Writing to the file is much faster than writing to the DB. And, since we are talking about mySQL here the thread safety and data intergation is pretty much similar. my regards to your interestind question -- Maxim Maletsky maxim@php.net