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...

Debbie

r937
—
2012-07-19T01:48:28Z —
#6

DoubleDee said:

... my script was crashing because multiple records were being INSERTED with the same Time-Stamp.

crashing? whoa

that sounds like you have the timestamp defined as unique by itself

the uniqueness constraint should be on the combination of (profile,timestamp)

so even if they click on another friend's profile within the same second, that would be a different log entry

and if they were to click on the same friend's profile within the same second, you can use INSERT IGNORE to handle that situation

this completely eliminates the need for sub-second log accuracy

you can comfortably continue using NOW() as before

or, as i prefer, use CURRENT_TIMESTAMP, which mysql supports, which is the standard sql equivalent of NOW()

and if they were to click on the same friend's profile within the same second, you can use INSERT IGNORE to handle that situation

this completely eliminates the need for sub-second log accuracy

Does that mean that one INSERT doesn't happen? (If so, I don't want that.)

Every time someone visits a Profile it needs to get logged.

And even if there wasn't this conflict issue, it might be nice to see a SUB-second in the "created_on" field.

Debbie

DoubleDee
—
2012-07-19T02:10:55Z —
#8

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

Debbie

r937
—
2012-07-19T02:12:15Z —
#9

DoubleDee said:

Every time someone visits a Profile it needs to get logged.

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

r937
—
2012-07-19T02:13:29Z —
#10

DoubleDee said:

... I have been taught that it is good to have some way to identify unique records based on physical attributes versus some arbitrary number.

i totally agree

that's what you use UNIQUE constraints for (or, in mysql's case, UNIQUE indexes)

DoubleDee
—
2012-07-21T02:39:07Z —
#11

r937 said:

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

Yeah, I try to disagree just to spite people...

Debbie

r937
—
2012-07-21T06:16:46Z —
#12

DoubleDee said:

Yeah, I try to disagree just to spite people...

you forgot the smiley

did you at least understand why i suggested the 3-column unique index instead of your 2-column?

DoubleDee
—
2012-07-21T14:41:12Z —
#13

r937 said:

you forgot the smiley

No.

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...

Sincerely,

Debbie

DoubleDee
—
2012-07-21T15:15:03Z —
#14

r937 said:

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)

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.

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.

Sincerely,

Debbie

r937
—
2012-07-21T15:16:35Z —
#15

DoubleDee said:

You constantly mistake my desire to find the best solutions and thoroughly vet responses from others as being argumentative.

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

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!!

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.

r937
—
2012-07-21T23:20:11Z —
#18

if i say well done, debbie, will you promise not to misinterpret it?

DoubleDee
—
2012-07-22T01:17:22Z —
#19

r937 said:

if i say well done, debbie, will you promise not to misinterpret it?

No, like most people, I respond very well to positive feedback. (Especially since I get so little in real life.)

Thanks everyone for the help and inspiration!

BTW, as always, there are lots of "right" answers. But for me, and being a perfectionist, the solution I just posted feels the best to me.

Sincerely,

Debbie

Jeff_Mott
—
2012-07-22T04:01:32Z —
#20

DoubleDee said:

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?!

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.