Surprising: Where a quote usage dramatically slows down a request

[sdeluca]

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

[srynonick]

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.

[sdeluca]

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?

[srynonick]

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

[Peter]

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.