Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 6 of 6
  1. #1
    New Coder
    Join Date
    Jun 2012
    Posts
    26
    Thanks
    9
    Thanked 0 Times in 0 Posts

    Simple insert query taking 1-5 second(s)

    Hi,
    Can anyone lighten me up why this simple query is taking so much time?
    The table has very few rows (like 500-700) and only 3 columns.

    Table properties:
    Index:
    keyname: user
    BTREE
    Column name: user

    _______
    Query:
    Query_time: 2.008483 Lock_time: 0.000085 Rows_sent: 0 Rows_examined: 0
    use mydatabase;
    SET timestamp=1366448462;
    INSERT INTO `tablename` (user) VALUES('74763')
    _______

    Isnt it irritating to see this query taking 1-5 seconds?
    Any advice?
    Last edited by sanomani; 04-20-2013 at 12:15 PM.

  • #2
    Senior Coder
    Join Date
    Jan 2011
    Location
    Missouri
    Posts
    4,225
    Thanks
    23
    Thanked 606 Times in 605 Posts
    Try this

    update tablename set timestamp=1366448462 where user=74763;
    Evolution - The non-random survival of random variants.

    "If you leave hydrogen alone, for long enough, it begins to think about itself."

  • #3
    New Coder
    Join Date
    Jun 2012
    Posts
    26
    Thanks
    9
    Thanked 0 Times in 0 Posts
    I think I confused you with the word Query.

    Actually i got this report from my mysql slow_query_log:
    Time to execute: 2.008483 seconds
    Lock_time: 0.000085
    Rows_sent: 0
    Rows_examined: 0
    Database used: mydatabase;

    Query was: "INSERT INTO `tablename` (user) VALUES('74763')"


    Additionally, can you tell which query should run faster?
    "update `table` set `point` = '100'"
    or
    "update `table` set `point` = `point` + '1'"
    Last edited by sanomani; 04-20-2013 at 04:39 PM.

  • #4
    Senior Coder
    Join Date
    Jan 2011
    Location
    Missouri
    Posts
    4,225
    Thanks
    23
    Thanked 606 Times in 605 Posts
    @sanomani, Old Pedant is back and will give you a far better answer than i did or will.
    Evolution - The non-random survival of random variants.

    "If you leave hydrogen alone, for long enough, it begins to think about itself."

  • #5
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,591
    Thanks
    78
    Thanked 4,385 Times in 4,350 Posts
    Back from the front?

    Actually, I don't understand it, either.

    I will say that you should not put apostrophes around numeric values (e.g, '74763') unless the field you are inserting into (in this case, user) is *NOT* a numeric data type.

    But that shouldn't cause more than a millisecond or two of extra time for a single insert.

    I am wondering if perhaps you are doing this on an overloaded server.

    Instead of looking at the query time, look at the lock time: 0.000085, or 85 MICROseconds. In other words, that's all the time that MySQL used to lock the needed DB elements while performing the INSERT. So where did the other 2 seconds go? Possibly into simply waiting until the table was unlocked by some other user or process?

    Are other kinds of queries on this database similarly slow? Or is it just the inserts?
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #6
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,591
    Thanks
    78
    Thanked 4,385 Times in 4,350 Posts
    Quote Originally Posted by sanomani View Post
    Additionally, can you tell which query should run faster?
    "update `table` set `point` = '100'"
    or
    "update `table` set `point` = `point` + '1'"
    Once again, I have to ask: Why didn't you simply TRY IT???

    I would expect them to both run at nearly identical speed.

    You are changing the value of the field point in EACH AND EVERY RECORD of the table. So MySQL has no choice but to simply scan the entire table and change every record. I doubt seriously that the slight extra overhead of fetching the field value before changing it will ever be noticeable.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.


  •  

    Posting Permissions

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