The answer is it depends,
Frankly, according to your results, I would rather answer the difference is
marginal.
~|
Order the Adobe Coldfusion Anthology now!
Rex, wonderful results. Thanks.
On Tue, Sep 14, 2010 at 9:48 PM, rex li...@pgrworld.com wrote:
Over 100,000 queries this is what I got (in ms):
LIKELEFT
MyISAM 16,215.60 16,069.00
InnoDB 16,168.60 15,987.80
MSSQL Server 28,268.60 26,775.20
Over 100,000 queries this is what I got (in ms):
LIKELEFT
MyISAM 16,215.60 16,069.00
InnoDB 16,168.60 15,987.80
MSSQL Server 28,268.60 26,775.20
Won's Test 1,184.401,168.80
INDEXED MyISAM 16,256.20 16,303.20
INDEXED InnoDB
Interesting. Thanks for the results. There probably is another route we
could test. We could right a function in C, compile it , and add it to
mysql. I've never done it myself but I did read that it may make your query
faster. I think I also I read that it might slow it down.
BTW according
With SQL Server, DEFINITELY go with left(str, 4) = 'string'
It has much less processing overhead.
-Original Message-
From: Michael Grant [mailto:mgr...@modus.bz]
Sent: Wednesday, September 08, 2010 1:20 PM
To: cf-talk
Subject: WHERE Left(str,5) = 'string' VS WHERE str LIKE 'string%'
What about mySQL?
Do you know if this is documented and easy to find?
On Wed, Sep 8, 2010 at 1:23 PM, DURETTE, STEVEN J (ATTASIAIT)
sd1...@att.com wrote:
With SQL Server, DEFINITELY go with left(str, 4) = 'string'
It has much less processing overhead.
-Original Message-
From:
On Wed, Sep 8, 2010 at 1:27 PM, Michael Grant mgr...@modus.bz wrote:
What about mySQL?
Do you know if this is documented and easy to find?
http://dev.mysql.com/doc/refman/5.6/en/mysql-indexes.html
~|
Order the Adobe
turn on debug mode, run both versions and then look at the execution time of
the cfquery, this will show you which processed faster.
On Wed, Sep 8, 2010 at 7:11 PM, Won Lee won...@gmail.com wrote:
On Wed, Sep 8, 2010 at 1:27 PM, Michael Grant mgr...@modus.bz wrote:
tioin
What about
Great suggestion. Thanks.
On Wed, Sep 8, 2010 at 2:26 PM, Russ Michaels r...@michaels.me.uk wrote:
turn on debug mode, run both versions and then look at the execution time
of
the cfquery, this will show you which processed faster.
On Wed, Sep 8, 2010 at 7:11 PM, Won Lee
In SQL Server go with like str%. The reason is that like str% is
sargable and functions are not. Functions also have overhead that
native set-based SQL does not. I would assume the same is true with
mySQL. Native SQL is usually faster than functions as a general rule,
unless the equivalent SQL is
Hmmm. That seems to conflict with what Steven says. Perhaps a blood match is
in order?
On Wed, Sep 8, 2010 at 3:10 PM, Mike Chabot mcha...@gmail.com wrote:
In SQL Server go with like str%. The reason is that like str% is
sargable and functions are not. Functions also have overhead that
, 2010 3:10 PM
To: cf-talk
Subject: Re: WHERE Left(str,5) = 'string' VS WHERE str LIKE 'string%'
In SQL Server go with like str%. The reason is that like str% is
sargable and functions are not. Functions also have overhead that
native set-based SQL does not. I would assume the same is true with
mySQL
Or, if anyone really cares, just write both the queries, fire up the
Query Profiler (for MSSQL) and see what the execution plans say.
On Wed, Sep 8, 2010 at 12:12 PM, Michael Grant mgr...@modus.bz wrote:
Hmmm. That seems to conflict with what Steven says. Perhaps a blood match is
in order?
mike,
Please let us know what you find out. I'm very curious of this myself. As
the document clearly states, mysql will use an index when you use a like but
don't start the string with a wildcard. So we know that
Left(str,5) = 'string' VS WHERE str LIKE 'string%'both will use an index.
The
Yes I did. Apparently I can't count. :D
On Wed, Sep 8, 2010 at 3:45 PM, Won Lee won...@gmail.com wrote:
mike,
Please let us know what you find out. I'm very curious of this myself. As
the document clearly states, mysql will use an index when you use a like
but
don't start the string
Getting to actually test this hasn't been as easy as I'd hoped. If I can get
something definitive I'll post.
On Wed, Sep 8, 2010 at 3:45 PM, Won Lee won...@gmail.com wrote:
mike,
Please let us know what you find out. I'm very curious of this myself. As
the document clearly states, mysql
I ran a quick test
CREATE TABLE HoF (
ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
LastName VARCHAR(100)
) ENGINE = InnoDB;
insert into HoF (LastName) values ('Smith');
insert into HoF (LastName) values ('Smithville');
insert into HoF (LastName) values ('Jones');
17 matches
Mail list logo
