leaning towards LIKE rather than FULLTEXT

[StuartBombay]

I thought fulltext sounded like the way to go at first, but now I think it's not the best option when searching for proper names. I want to be able to search against first, last, email username or employee number. Ideally it could find based on part of a name or employee number.I tried putting fulltext indexes on all those fields but had 2 problems. First was if I wrote the query to search more than 1 colum:

select * from people where match(username, emplid) against ('drknow')

I get the error '#1191 - Can't find FULLTEXT index matching the column list'. Also, I had to have the entire name or employee number - including leading 0's.

I can't figure out why I get that error, but maybe it's moot because I shouldn't use FULLTEXT anyway?
If I use a LIKE query instead, how many columns can I reasonable search on, and do I need to index those columns? Would it be with 'INDEX' then?
BTW, here is the table:

CREATE TABLE IF NOT EXISTS people (
  username varchar(15) NOT NULL,
  emplid varchar(10) default NULL,
  `first` varchar(30) NOT NULL,
  middle varchar(10) default NULL,
  `last` varchar(40) NOT NULL,
  `full` varchar(50) NOT NULL,
  locnum varchar(10) default NULL,
  prognum varchar(10) default NULL,
  positionnum varchar(10) default NULL,
  jobcode varchar(10) default NULL,
  PRIMARY KEY  (username),
  KEY emplid (emplid),
  FULLTEXT KEY emplidFT (emplid),
  FULLTEXT KEY fullFT (`full`),
  FULLTEXT KEY usernameFT (username)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Yeah, that 'full' is a problem. I need to change it to 'preferred' before I write much more code!

I appreciate any input/suggestions. Maybe there's an option I haven't considered, I'm pretty green at this.

 

[r937]

fulltext searching requires that there be a fulltext index on the columns you are searching -- not a fulltext index on each of them individually, but a fulltext index on the combination that you plan to search on

the fact that you don't have a fulltext index on (username, emplid) would explain your error

does that help?

r937.com | rudy.ca

 

[StuartBombay]

Yes, that does make sense. Thank you. I thought I had read that, but I couldn't see how to make MySqlAdmin combine columns in an index using it's interface.
So, I could remove those fulltext indexes and use the SQL window to do something like -

update people set...
  FULLTEXT KEY PLPSEARCH (emplid,`full`,username)

I'll figure out the exact syntax. Do you think that, the data being names rather than dictionary words, FULLTEXT is a better choice than LIKE?
I also need to search another table for school names, I'm not sure which would be best there either. Now that I know how to do the FULLTEXT I can at least experiment.

 

[StuartBombay]

Doh! 'Go' to add columns! (head slap)