Currently I am running a concat statement to combine a field with a user
name and domain to create and email address. In testing it looks like
running the concat is a very slow command to run. The select statement
currently looks like this.

select concat(user,'@',domain),servername,port from database where
concat(user,'@',domain)='username@domain.com';

On 10/24/07, Gerard <gerard.cluster@gmail.com> wrote:
> Currently I am running a concat statement to combine a field with a user
> name and domain to create and email address. In testing it looks like
> running the concat is a very slow command to run. The select statement
> currently looks like this.
>
> select concat(user,'@',domain),servername,port from database where
> concat(user,'@',domain)='username@domain.com';
>
That query will be very slow because mysql will have to examine each
row. You would be far better served to do something like
select concat(user,'@',domain),servername,port
from database
where
user = substring('username@domain.com',0,LOCATE('@','user name@domain.com'))
AND
domain = substring('username@domain.com',LOCATE('@','userna me@domain.com'))

or something like that, or even better split it outside mysql if possible.
--
Rob Wultsch
(480)223-2566
wultsch@gmail.com (email/google im)
wultsch (aim)
wultsch@hotmail.com (msn)

Gerard wrote:
> Currently I am running a concat statement to combine a field with a user
> name and domain to create and email address. In testing it looks like
> running the concat is a very slow command to run. The select statement
> currently looks like this.
>
> select concat(user,'@',domain),servername,port from database where
> concat(user,'@',domain)='username@domain.com';
>

Why do CONCAT() twice? Couldn't you just do:

WHERE user = 'username' AND domain = 'domain.com'

Or am i missing something?

brian

On 10/24/07, mysql@subtropolix.org <mysql@subtropolix.org> wrote:
>
> Gerard wrote:
> > Currently I am running a concat statement to combine a field with a user
> > name and domain to create and email address. In testing it looks like
> > running the concat is a very slow command to run. The select statement
> > currently looks like this.
> >
> > select concat(user,'@',domain),servername,port from database where
> > concat(user,'@',domain)='username@domain.com';
> >
>
> Why do CONCAT() twice? Couldn't you just do:
>
> WHERE user = 'username' AND domain = 'domain.com'
>
> Or am i missing something?
>
> brian
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql?unsub=g...ster@gmail.com



This is done because the application is not flexible. I can only put one
condition in which goes for the where and select statement.