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
    Apr 2010
    Posts
    14
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Question Faster Mysql Update

    I dont konw if I need to post this here or in the Mysql forum but.. here is my questions:

    1. I have php code that use mysqli and I want to make the update statments faster, if I add DISABLE KEYS it works X2 faster , but sometimes he not updating anything on the DB, I dont konw way.

    Can someone explain me where should I use DISABLE KEYS and why its make the update faster?

    2. I tried to use prepare statments for faster updates, but its not working inside the functions.. i want it to be like this:

    <?php
    prepare update statment (outside the functions)

    function name($somthing)
    {
    code...
    exceute the prepared statment..
    code..
    }

    code not in function..
    calling to the function..
    ?>

    I even try to put the prepard statment inside the () of the function and the call but its not work.. how can I use inside function prepard statment from outside the function?

  • #2
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,978
    Thanks
    4
    Thanked 2,659 Times in 2,628 Posts
    Something smells of bad structure.
    If you're using disabled keys, than you are using a myisam table. I would convert to innodb and then use a prepared statement. I would not disable keys in any way, shape or form.
    If your function isn't working with a prepared statement, it is because you have not written it correctly. Since you don't provide us with any code, we cannot provide you with any solution.

  • #3
    New Coder
    Join Date
    Apr 2010
    Posts
    14
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Fou-Lu View Post
    Something smells of bad structure.
    If you're using disabled keys, than you are using a myisam table. I would convert to innodb and then use a prepared statement. I would not disable keys in any way, shape or form.
    If your function isn't working with a prepared statement, it is because you have not written it correctly. Since you don't provide us with any code, we cannot provide you with any solution.
    innodb is slower the myisam no? why the use of the disabled key make the update faster?

    and here is my code:

    the prepared update: (outside the function)
    $something= $mysqli->prepare("UPDATE table_name SET row_name=row_name+1 WHERE id=? LIMIT 1");

    the code inside the function:
    $something->bind_param("i",$some_int);
    $something->execute();

    and the calling: (same in the function head)
    function_name($mysqli,$something);

  • #4
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,978
    Thanks
    4
    Thanked 2,659 Times in 2,628 Posts
    InnoDB will beat MyISAM hands down. It also gains a lot of functionality that myisam just will not support.
    This code is still absolutely useless. Post the PHP code in use.

    As for keys, I'd expect that your disabling of them would make an insertion or update faster since it doesn't need to update the indexing. Of course, this will cripple your selections.

  • #5
    Rockstar Coder
    Join Date
    Jun 2002
    Location
    USA
    Posts
    9,074
    Thanks
    1
    Thanked 328 Times in 324 Posts
    So how long do the updates take now? Like an actual time value? Beyond how your are writing your queries, there is always the possibility that your MySQL server is incorrectly configured for the amount of data you are working with or the server load. However if you are running on a shared host, this probably isn't the case.
    OracleGuy

  • #6
    New Coder
    Join Date
    Apr 2010
    Posts
    14
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by oracleguy View Post
    So how long do the updates take now? Like an actual time value? Beyond how your are writing your queries, there is always the possibility that your MySQL server is incorrectly configured for the amount of data you are working with or the server load. However if you are running on a shared host, this probably isn't the case.
    I'm on hostgator.
    and for the other question update take 0.0002sec , and if i use DISABLED KEYS it take 0.0001sec i konw that its sound the same but its really important. I need it to be 0.0001sec or less(but with disabled keys its make problem like i wrote before)


  •  

    Posting Permissions

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