Capture Fraction of a Second

[DoubleDee]

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

Here is the information I log into MySQL…

	$q1 = "INSERT INTO visitor_log(member_viewed, visitor_id, ip, hostname, created_on)
				VALUES(?, ?, ?, ?, NOW())";

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…

Thanks,

Debbie

[system]

MySQL 5.6.4 introduces support for fractional seconds: http://dev.mysql.com/doc/refman/5.6/en/news-5-6-4.html

[rcashell]

Hi, if you look at NOW() function in MySQL (from 5.0) manual you will see that this returns not only date time but also microseconds if you use the datetime as a numeric rather than a string.http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_now

[DoubleDee]

@itmitică;5153260

MySQL 5.6.4 introduces support for fractional seconds: http://dev.mysql.com/doc/refman/5.6/en/news-5-6-4.html

Unfortunately I am running MAMP which has MySQL v5.0.41 so that won’t help.

[DoubleDee]

@rcashell

Hi, if you look at NOW() function in MySQL (from 5.0) manual you will see that this returns not only date time but also microseconds if you use the datetime as a numeric rather than a string.http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_now

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]

@DoubleDee

… 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()

[DoubleDee]

@r937

crashing? whoa

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

Here are the indexes I created…

Indexes:

Keyname		Type		Cardinality	Field
-------------	---------	------------	-----------
PRIMARY		PRIMARY 	928  		id

idx_u_comment 	UNIQUE		928		visitor_id
						created_on

idx_member_id 	INDEX		46		visitor_id

I don’t remember why I did that - probably because there is no natural or derived PK…

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

This is happening when I click on “Random Friends” in the user’s Profile.

When I am in my Profile, I see a handful of Friends. And when I go to their Profiles, they just have me as a Friend.

So the sequence is…

DoubleDee —> JaneDoe —> DoubleDee —> SunshineMan —> DoubleDee —> and so on…

So I am trying to INSERT my memberID and the same TimeStamp twice.

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]

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]

@DoubleDee

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]

@DoubleDee

… 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]

@r937

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]

@DoubleDee

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]

@r937

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]

@r937

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.

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.

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
1	19		19		127.0.0.1	localhost	2012-07-21 07:58:14

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]

@DoubleDee

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

[r937]

@DoubleDee

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
 1       19           19        127.0.0.1 localhost 2012-07-21 07:58:14

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

[DoubleDee]

So here is what I ended up doing… (A good mental calisthenic!!)

1.) I created this PHP Function…

	function getDateTimeMicroTime(){
		/**
		 * Create Date-Timestamp with Micro-Seconds.
		 *
		 * Written On: 2012-07-21
		 *
		 * @return	String
		 */

		list($microSec, $timeStamp) = explode(" ", microtime());
		return (date('omdhis', $timeStamp) . substr($microSec, 1));

		//Ex: 20120721122038.820
	}

2.) When someone visits a User’s Profile, I call my PHP logVisitor() function, which performs an INSERT using the new function above.

3.) I changed the “created_on” field in my “visitor_log” table from “DATETIME” to “DECIMAL(17,3)” data-type.

4.) I created this Unique Index…

Keyname		Type		Cardinality	Field
-----------	-------		------------	--------------
idx_u_visit	UNIQUE		87		member_viewed
						visitor_id
						created_on

Finally, I ran a quick test and got these logged results with no errors whatsoever!!!

Here is my output…

id	member_viewed	visitor_id	ip		hostname	created_on		updated_on
---	-------------	----------	---------	---------	------------------	----------
815	19		0		127.0.0.1	localhost	20120721122219.080	NULL
816	19		0		127.0.0.1	localhost	20120721122219.210	NULL
817	19		0		127.0.0.1	localhost	20120721122219.340	NULL
818	19		0		127.0.0.1	localhost	20120721122219.500	NULL
819	19		0		127.0.0.1	localhost	20120721122219.620	NULL
820	19		0		127.0.0.1	localhost	20120721122219.750	NULL
821	19		0		127.0.0.1	localhost	20120721122219.900	NULL

Was all of that necessary? No.

Was it overkill? Maybe.

Did I learn some fancy new PHP and MySQL 5.0 tricks? Yes!!

Is this a better solution than I originally had? Definitely!

---

Oh, and if I want “pretty” output, I just run a query like this…

SELECT timestamp(created_on) AS created_on
FROM visitor_log

And I get output like this in MySQL…

created_on
----------------------------------
2012-07-21 12:22:19.080000
2012-07-21 12:22:19.210000
2012-07-21 12:22:19.340000
2012-07-21 12:22:19.500000
2012-07-21 12:22:19.620000
2012-07-21 12:22:19.750000
2012-07-21 12:22:19.900000

Sincerely,

Debbie

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]

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

[DoubleDee]

@r937

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]

@DoubleDee

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.