I have a php script which logs every time a person visits someone’s Profile. (This could be a Registered Member or an Anonymous Visitor visiting someone’s Profile.)
The problem that I discovered last night, however, is that if someone is rapidly clicking through other people’s Profiles under their “Random Friends” or “Last 10 Visitors” section, my script was crashing because multiple records were being INSERTED with the same Time-Stamp.
This problem could be fixed by removing the “Unique Index” on the table, but the larger issue is that I need more granularity than NOW() provides.
[b]So, is there a way to capture all of the information that now() currently captures (i.e. Date & Time), but also capture Fractions of Seconds as well?
If so, how would I do this?[/b]
I didn’t see anything in the manual that captures Date + Time + Micro-Seconds…
I saw that in the Manual as well, but I don’t understand what that means… :-/
Whatever I do, the Date/Time should be in a format that is readable, or that can easily be made readable. And it should also be in a format that can be recognized as a valid Date/Time by any PHP code touching that field.
The example the Manual almost looks like a Unix Timestamp, which isn’t very friendly in MySQL…
If I created a derived PK (i.e. autonumber) then I guess I could remove the Unique Index and the problem would be fixed, but I have been taught that it is good to have some way to identify unique records based on physical attributes versus some arbitrary number. Otherwise you run into…
ID Full Name
--- -------------
1 John Doe
2 John Doe
3 John Doe
4 Bob Smith
5 John Doe
i agree, but you’re logging every time someone visits ~any~ profile, and i think it should be more specific
you’re logging visitor_id + created_on as unique, and i’m assuming “created_on” is the column at issue here, the timestamp of when the action was made that you’re logging
it should be visitor_id (you) + created_on + profile_id (the profile you’re visiting)
you might not agree, as is your prerogative, but that’s how i’d do it
did you at least understand why i suggested the 3-column unique index instead of your 2-column?
Yes, I understand what you are saying, and AS USUAL I think you are RIGHT from a technical standpoint.
But also usual, I just didn’t like your side commentary.
You constantly mistake my desire to find the best solutions and thoroughly vet responses from others as being argumentative. That bothers me, because there is never any malice on my part…
I am just skeptical and realize that 90-95% of the information online is wrong, so it is my nature to be sure things are correct before I blindly accept them.
Since you are clearly an EXPERT with all things databases, I tend to take 90% of what you advise as “gospel”, because I know you are almost always right. (The reason why your comment was even less needed.)
But other than advice from people like you and Paul O’, yes, I do tend to question things a lot…
I like your idea above, r937, and again, I think you present a better idea for Indexing than I had.
However, on second thought, your suggestion - while better from a database design standpoint - still doesn’t fix my application issue, and here is why…
First allow me to explain a little more about how things work.
If a User’s Profile is being viewed, on the left side is a section called “Random Friends” which displays thumbnails of some of that User’s Friends. Each thumbnail is a hyperlink which when clicked takes you to that new User’s Profile.
You do not need to be a Member or Logged In to view someone’s Profile. For instance, you could be new to the site, have just read an Article, and in the Comments section below, click on a Member’s Picture, which would take you to that Member’s Profile, and then you could click on that Member’s Random Friends, taking you to that Friend’s Profile, and so on…
Let’s say - just for fun - that “DoubleDee” and “r937” are friends. And that we only have each other as friends.
And, as a reminder, every time someone views a Member’s Profile, I am inserting a record in to the “visitor_log” table, including “anonymous” Visitors where in that case I grab their IP Addy and Host Name.
So, back to the issue at hand…
DoubleDee’s Profile is being displayed. (It doesn’t matter who is looking at it, but for simplicity, let’s assume I am logged in as “DoubleDee”.) And so I do an INSERT to note that DoubleDee’s Profile is being viewed.
So we have…
id member_viewed visitor_id ip hostname created_on
1 19 19 127.0.0.1 localhost 2012-07-21 07:58:14
Remember that I clicking VERY QUICKLY…
Now I click on Double’s only friend, “r937” and am taken to his Profile.
So we have…
id member_viewed visitor_id ip hostname created_on
1 19 19 127.0.0.1 localhost 2012-07-21 07:58:14
1 55 19 127.0.0.1 localhost 2012-07-21 07:58:14
Taking your advice, I have no Index conflicts currently because “member_viewed” + “visitor_id” + “crated_on” is unique at this point. (With my original way, my code would have crashed.)
Now I am viewing r937’s Profile and I click back on my picture and am taken back to DoubleDee’s Profile.
And, BAM, the new Index fails and my PHP throws an error because “Affected Rows” does NOT equal 1 because MySQL is trying to INSERT a 3rd record which happens to be identical to the 1st record!!
Could this happen in real life?
Of course, because I crashed my own website browsing Profiles rapidly…
Solutions:
1.) Drop “created_on” from the index since it cannot be used to guarantee uniqueness.
2.) Add a Fraction of a Second to the Time-Stamp which is precise enough that rapidly clicking through Profiles would never created a duplicate. (It is easy to view 3 Profiles in ONE SECOND. But adding even on decimal place to the Second spot would likely be enough. Adding two decimal place would almost certainly be enough, and so on.)
3.) I could just create an “id” auto-increment fiend and call it quits, but like I said above, I think it is better to be able to distinguish each record naturally in the physical world versus some derived ID.
4.) Do something else?!
That is the problem I am facing.
I think finding a way to add a fraction of a second onto the Date/Time time-stamp would make the most sense, but I’m always open to suggestion.
sorry, debbie, but you are very argumentative at times
not always, but you certainly have been at times, and i assure you, i am not the only one who thinks so
forgive me if i’ve begun to temper some of my responses with phrases like “you might not agree, as is your prerogative” based on previous reactions from you
i think i’ll just go back to very brief responses consisting of just bottom line facts without any commentary whatsoever
i dispute your assertion that it is “easy” to view 3 profiles in ONE SECOND
using the 3-column uniqueness (member_viewed,visitor_id,created_on) together with INSERT IGNORE means that the 3rd row in your example would not get logged
now think about this for a second… the same person managed to click on the exact same profile more than once in the same second, and you ignore all but one of those log entries – what have you really lost in terms of meaningful information?
and if you are ~still~ concerned about this, you might consider adding a counter to the log, which counts the number of identical clicks on the same profile by the same person within the same second – use ON DUPLICATE KEY UPDATE to increment the counter
P.S. r937 said he found it hard to believe that anyone could access a User Profile 3 times in a second. He is right, because my slow hands did it 7 times in a second.
I’d opt for option 4. I think the best option is to drop the unique index altogether. For performance, you can still use a regular index, but a unique index has only drawbacks and no benefits. As you noted, Debbie, a user technically could perform identical actions – the same user views the same profile at the same time – but a unique index means one of those logs either won’t be recorded or will throw an error.