SitePoint Sponsor

User Tag List

Results 1 to 20 of 20
  1. #1
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,777
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Capture Fraction of a Second

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

    Code:
    	$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.

    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?


    I didn't see anything in the manual that captures Date + Time + Micro-Seconds...

    Thanks,


    Debbie

  2. #2
    Non-Member
    Join Date
    Feb 2012
    Posts
    892
    Mentioned
    10 Post(s)
    Tagged
    0 Thread(s)
    MySQL 5.6.4 introduces support for fractional seconds: http://dev.mysql.com/doc/refman/5.6/en/news-5-6-4.html

  3. #3
    SitePoint Enthusiast
    Join Date
    Jul 2007
    Location
    San Sebastian, Spain
    Posts
    93
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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/...l#function_now

  4. #4
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,777
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by itmitică View Post
    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.

  5. #5
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,777
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by rcashell View Post
    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/...l#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

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,262
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by DoubleDee View Post
    ... 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()

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,777
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    crashing? whoa

    that sounds like you have the timestamp defined as unique by itself
    Here are the indexes I created...


    Code:
    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

  8. #8
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,777
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    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...

    Code:
    ID	Full Name
    ---	-------------
    1	John Doe
    2	John Doe
    3	John Doe
    4	Bob Smith
    5	John Doe

    Debbie

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,262
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by DoubleDee View Post
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,262
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by DoubleDee View Post
    ... 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)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  11. #11
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,777
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    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

  12. #12
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,262
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by DoubleDee View Post
    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?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  13. #13
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,777
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    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

  14. #14
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,777
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    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...

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

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

    Code:
    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

  15. #15
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,262
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by DoubleDee View Post
    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

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  16. #16
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,262
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by DoubleDee View Post
    Code:
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  17. #17
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,777
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    So here is what I ended up doing... (A good mental calisthenic!!)

    1.) I created this PHP Function...

    PHP Code:
        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($microSec1));

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

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

    Code:
    SELECT timestamp(created_on) AS created_on
    FROM visitor_log

    And I get output like this in MySQL...
    Code:
    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.

  18. #18
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,262
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    if i say well done, debbie, will you promise not to misinterpret it?

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  19. #19
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,777
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    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

  20. #20
    SitePoint Wizard bronze trophy Jeff Mott's Avatar
    Join Date
    Jul 2009
    Posts
    1,272
    Mentioned
    18 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by DoubleDee View Post
    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.
    "First make it work. Then make it better."


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •