MySQL Performance: MySQL => Benchmark needed: WHERE id='$id' ... vs. WHERE id=$id

Members

Help

Home

Home » Performance » MySQL » Benchmark needed: WHERE id='$id' ... vs. WHERE id=$id

Show: Today's Messages :: Show Polls :: Message Navigator

Benchmark needed: WHERE id='$id' ... vs. WHERE id=$id [message #1573]

Mon, 06 August 2007 11:19

Spuerhund
Messages: 4
Registered: August 2007

Junior Member

Has somebody ever run a test to measure time differences of queries like:

SELECT foo FROM bar WHERE id='$id'

and

SELECT foo FROM bar WHERE id=$id

assumed that column id is an integer?

What i would like to know is how mutch these '...' influence the evaluation. I bet there is some difference (some CPU cycles needed for the string-to-integer cast) and i guess that it is really small compared to the time needed for seeking, ordering, joining, etc...

Nevertheless it could be interesting to know. Maybe the difference would be bigger when several expressions are combined like:

SELECT foo FROM bar WHERE a='$a' AND b='$b' AND c='$c' AND d='$d'

against

SELECT foo FROM bar WHERE a=$a AND b=$b AND c=$c AND d=$d

Anybody who can offer some statistics?

[Updated on: Mon, 06 August 2007 11:20]

Report message to a moderator

Re: Benchmark needed: WHERE id='$id' ... vs. WHERE id=$id [message #1577 is a reply to message #1573 ]

Tue, 07 August 2007 12:58

Speeple
Messages: 91
Registered: August 2006

Member

Without the quotes is required for the column to be compared as an integer. Otherwise, MySQL may be forced to cast the internally stored id integer as a string type before comparing it to the value in the quotes.

This would mean indexes wouldn't be utalized and the query wouldn't run optimally.

Martin Gallagher | Speeple: The latest news

Report message to a moderator

Re: Benchmark needed: WHERE id='$id' ... vs. WHERE id=$id [message #1580 is a reply to message #1577 ]

Thu, 09 August 2007 07:17

Spuerhund
Messages: 4
Registered: August 2007

Junior Member

Of course, without the quotes the query will fail if one of the columns is not an integer. Therefore i assumed that all columns are integers, i didn't want to benchmark error messages. Wink

I once checked a query with quotes via EXPLAIN and MySQL did use the primary index. Without quotes EXPLAIN showed the same information, so index usage was not affected by the '' and the cast.

Unfortunately i have neither the experience nor the environment to run serious benchmarks here and i have no bigger database which i could use for it (as i guess that with only 150 rows no difference can be seen). Maybe somebody with more experience, a dedicated MySQL server and a large database could help me by running this benchmark?

Report message to a moderator

Re: Benchmark needed: WHERE id='$id' ... vs. WHERE id=$id [message #1643 is a reply to message #1580 ]

Fri, 17 August 2007 06:41

Peter
Messages: 405
Registered: August 2006

Senior Member
Super Guru

Passing numbers in quotes normally works fine it is unquoted strings which is the problem

VARCHARCOL=1 and VARCHARCOL="1" have very different meaning.

In your case of integer columns you should have only minor parsing overhead - query execution will go same in most cases.

Peter Zaitsev, MySQL Performance Expert
MySQL Performance Blog - http://www.mysqlperformanceblog.com
MySQL Consulting http://www.mysqlperformanceblog.com/mysql-consulting/

Report message to a moderator

Previous Topic:	JOB: MySQL DBA - London Technology Company
Next Topic:	major connection spike during "source dumpfile.sql"

Goto Forum:

-=] Back to Top [=-

[ Generate printable PDF ] [ Syndicate this forum (XML) ] [

]

Current Time: Thu Jan 8 22:42:15 EST 2009

Total time taken to generate the page: 0.04762 seconds

.:: Contact :: Home :: MySQL Support by Percona.com ::.