While the query "WHERE expr LIKE '%$user%'" works without fail, it can
not use an index, and thus on large tables will be exceedingly slow.
mysql> explain select last, first from users where concat(last,first)
like '%user%'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: users
type: ALL <<<
possible_keys: NULL <<<
key: NULL
key_len: NULL
ref: NULL
rows: 3
Extra: Using where
1 row in set (0.01 sec)
Therefor, where it's possible, I would suggest adding a fulltext index
on (first, last), or what ever columns you need. This will handle the
concatenation of these two columns inside the index :)
mysql> alter table users add fulltext index (first, last);
Query OK, 3 rows affected (0.02 sec)
Records: 3 Duplicates: 0 Warnings: 0
Then use the query "WHERE MATCH expr AGAINST ('%$user%')".. like this...
mysql> explain select first, last from users where match (first,last)
against ('%user%')\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: users
type: fulltext <<<
possible_keys: first_2 <<<
key: first_2
key_len: 0
ref:
rows: 1
Extra: Using where
1 row in set (0.01 sec)
Hope that helps,
~Deva
Ed Reed wrote:
I do this all the time and it works flawlessly. Just like your example and even more extreme. I use this technique to provide search mechamisms for my applications. ex,
SELECT ID
FROM table
WHERE concat(field1, field2, field3,...{all the fields in the table}) Like '%searchstring%';
This works great for me. Is 'user' your actually table name and is it possible that the table name is being confused with the 'user' table in the MySQL database?
Good Luck
"Matt Babineau" <[EMAIL PROTECTED]> 6/23/05 1:50:12 PM >>>
Hey All-
Got a fun question - I hit the manual but not much luck on my question. I
want to combine 2 fields and then search them....
SELECT first_name, lastname FROM user WHERE CONCAT(first_name, ' ',
last_name) LIKE '%$user%'
Does this make sense? The CONCAT function was the closest I found to try and
do what I want to do. I alread tried this:
SELECT concat(first_name, ' ', last_name) as fullname FROM user...
This did not work. If anyone has any ideas on how to search for users when
the first_name and last_name fields are broken up I'm all ears!
Thanks,
Matt Babineau
Criticalcode
858.733.0160
[EMAIL PROTECTED]
http://www.criticalcode.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
