| Surprising: Where a quote usage dramatically slows down a request [message #1733] |
Tue, 04 September 2007 08:33  |
 sdeluca
Messages: 4
Registered: September 2007
Location: France |
Junior Member |
|
|
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
Come and enjoy http://mobilezoo.biz and http://boursomac.com
|
|
|
|
| Re: Surprising: Where a quote usage dramatically slows down a request [message #1738 is a reply to message #1735 ] |
Tue, 04 September 2007 19:36  |
sdeluca
Messages: 4
Registered: September 2007
Location: France |
Junior Member |
|
|
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?
Come and enjoy http://mobilezoo.biz and http://boursomac.com
|
|
|
|
| Re: Surprising: Where a quote usage dramatically slows down a request [message #1750 is a reply to message #1733 ] |
Wed, 05 September 2007 10:16 |
Peter
Messages: 405
Registered: August 2006 |
Senior Member
Super Guru |
|
|
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.
Peter Zaitsev, MySQL Performance Expert
MySQL Performance Blog - http://www.mysqlperformanceblog.com
MySQL Consulting http://www.mysqlperformanceblog.com/mysql-consulting/
|
|
|
Members
Search
Help
Register
Login
Home
