Announcement

Announcement Module
Collapse
No announcement yet.

Surprising: Where a quote usage dramatically slows down a request

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Surprising: Where a quote usage dramatically slows down a request

    Hi there,

    I wanted to subtmit a weird finding while optimizing our backend access (MySql 5.0.32-Debian_7etch1-log).

    I have a table with the primary indexed column user_id (mediumint ( 8 )).

    With the following code, we update some user's infos:


    UPDATE phpbb_users SET flastcellupload = '1188382472' , fidcell='10459' WHERE user_id = '10915207';Query OK, 0 rows affected (3.38 sec)Rows matched: 0 Changed: 0 Warnings: 0

    Note that user_id 10915207 does not exists (and card(user_id)=670+K).

    This request takes 3+s to be executed.

    And what if I remove the quote around the number, as follows?:

    UPDATE phpbb_users SET flastcellupload = '1188382472' , fidcell='10459' WHERE user_id = 10915207;Query OK, 0 rows affected (0.00 sec)Rows matched: 0 Changed: 0 Warnings: 0

    As you can see, it takes almost nothing to perform.

    I ran some similar tests on SELECT and I can say it has no effect (very quick in both syntaxex).

    Any toughts?

    Thanks in advance,
    Sdl

  • #2
    i don't know why its faster on select, but user_id is a number, integer, and therefore it shouldnt be user_id='x' but user_id=x, as 'x' means a string.

    Comment


    • #3
      thx srynonick for your answer. But I'm afraid a SQL query is by essence a string. So I dono why putting single quote should slow down that way.

      And, btw, I forgot to mention that the update does not suffer from slow down if I use an user_id which is smaller (for ex 600000).

      Does it have to do with mediumint?

      Comment


      • #4
        maybe mysql isn' that intelligent an makes a conversion to int (the value) or the content of the row (to string) on every row.

        Comment


        • #5
          Is it 100% repeatable ?
          (It could be first update was just uncached)

          If yes take a look at Handler_XXX increments while running first and second query

          If they are different file a bug with MySQL.

          String->Number conversion should work fine as it is deterministic.
          It is Number->String which usually slow things down.

          Comment

          Working...
          X