Ari Denison wrote:
Sticking with the May example... I would like to be able to return results for only students named May or Maya, but not Mayra, Jessica-May, or Maylita.
With a definite list, you can
SELECT * FROM students WHERE first_name IN ('May', 'Maya');This would use a simple index on first_name.
The other example would be simple wild cards. Something like steve* returns steve, steven, cody-steven, Stevenmikel, steve-allen.
Wildcards are slightly trickier. With full-text searching, the wildcard can only come on the right, so
SELECT * FROM students
WHERE MATCH (first_name) AGAINST ('steve*' IN BOOLEAN MODE);would match everything in your list, though perhaps not always the way you may think. For example, "cody-steven" only matches because the "-" is a word boundary character. That is, "cody-steven" is two words, "cody" and "steven". On the other hand, this query would not match a first_name of "Mikelsteven" or "esteven".
Without a full-text index, you can put wildcards on either side, but you can only use the index if the wildcard is on the right. You'd use queries such as
SELECT * FROM students WHERE first_name LIKE 'steve%';
which would select rows where first_name *starts* with 'steve'. This will use the index.
SELECT * FROM students WHERE first_name LIKE '%steve';
would select rows where first_name *ends* with steve, and
SELECT * FROM students WHERE first_name LIKE '%steve%';
or
SELECT * FROM students WHERE first_name RLIKE 'steve';
would select rows where first_name *contains 'steve'. These last 3 can't use an index on first_name.
Michael
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
