List:General Discussion« Previous MessageNext Message »
From:emierzwa Date:January 21 2004 8:59pm
Subject:RE: Efficient SQL Statement
View as plain text  
You could just use the benchmark function?

select BENCHMARK(10000000, 'dfsfsdfs' like 'F%' )   /* 0.45 sec. */
select BENCHMARK(10000000, 'dfsfsdfs' between 'F' and 'Fzzz' ) /* 0.55
sec. */
select BENCHMARK(10000000, left('dfsfsdfs',1)='F' ) /* 0.79 sec. */

The times go up a little if the strings are a match.

Ed

-----Original Message-----
From: Roger Baklund [mailto:roger@stripped] 
Sent: Wednesday, January 21, 2004 1:11 PM
To: mysql@stripped
Cc: Hassan Shaikh
Subject: Re: Efficient SQL Statement


* Hassan Shaikh
> Which one of the following statements is more efficient?
>
> SELECT * FROM COUNTRY WHERE LEFT(CNNAME,1)='B';
>
> Or
>
> SELECT * FROM COUNTRY WHERE CNNAME LIKE 'B%';

The second statement will normally be the most effective, because the
server
don't need to perform a function on the column for each row. However,
the
LIKE operator is relatively "heavy", the _most_ effective in this case
is
probably:

SELECT * FROM COUNTRY WHERE CNNAME>='B' AND CNNAME<'C'

 or

SELECT * FROM COUNTRY WHERE CNNAME BETWEEN 'B' AND 'Bzzz'

--
Roger


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/mysql?unsub=1

Thread
Efficient SQL StatementHassan Shaikh21 Jan
  • Re: Efficient SQL StatementRoger Baklund21 Jan
RE: Efficient SQL Statementemierzwa21 Jan
  • Re: Efficient SQL StatementRoger Baklund21 Jan
RE: Efficient SQL Statementemierzwa21 Jan
  • Re: Efficient SQL StatementRoger Baklund21 Jan
    • Re: Efficient SQL StatementChuck Gadd22 Jan
      • Re: Efficient SQL StatementRoger Baklund22 Jan