In this ssql query, I am using a % in the AGAINST clause.
SELECT firstname,lastname,middlename,company_name,
primary_emailaddress,alternate_emailaddress,personal_address_line1,
personal_address_line2,personal_address_city,facebook_username,
twitter_username,googleplus_username,linkedin_username,
personal_website_url,birthday_month,notes,personal_address_zipcode,
company_address_zipcode,home_phonenumber,company_phonenumber,
cell_phonenumber,birthday_day,birthday_year,hash,image_file
FROM contacts
WHERE (
MATCH(
firstname,middlename,lastname,
primary_emailaddress,alternate_emailaddress,personal_address_line1,
personal_address_city,company_name,
company_address_line1,company_address_city,
facebook_username,twitter_username,googleplus_username,linkedin_username,
personal_website_url,birthday_month,notes
)
AGAINST ('someemail@email.com%' IN BOOLEAN MODE)
OR personal_address_zipcode REGEXP('(someemail@email.com*)')
OR company_address_zipcode REGEXP('(someemail@email.com*)')
OR home_phonenumber REGEXP('(someemail@email.com*)')
OR company_phonenumber REGEXP('(someemail@email.com*)')
OR cell_phonenumber REGEXP('(someemail@email.com*)')
OR birthday_day REGEXP('(someemail@email.com*)')
OR birthday_year REGEXP('(someemail@email.com*)')
)
AND addressbook_id = 4
In this ssql query, I am using a * in the AGAINST clause.
SELECT firstname,lastname,middlename,company_name,
primary_emailaddress,alternate_emailaddress,personal_address_line1,
personal_address_line2,personal_address_city,facebook_username,
twitter_username,googleplus_username,linkedin_username,
personal_website_url,birthday_month,notes,personal_address_zipcode,
company_address_zipcode,home_phonenumber,company_phonenumber,
cell_phonenumber,birthday_day,birthday_year,hash,image_file
FROM contacts
WHERE (
MATCH(
firstname,middlename,lastname,
primary_emailaddress,alternate_emailaddress,personal_address_line1,
personal_address_city,company_name,
company_address_line1,company_address_city,
facebook_username,twitter_username,googleplus_username,linkedin_username,
personal_website_url,birthday_month,notes
)
AGAINST ('someemail@email.com*' IN BOOLEAN MODE)
OR personal_address_zipcode REGEXP('(someemail@email.com*)')
OR company_address_zipcode REGEXP('(someemail@email.com*)')
OR home_phonenumber REGEXP('(someemail@email.com*)')
OR company_phonenumber REGEXP('(someemail@email.com*)')
OR cell_phonenumber REGEXP('(someemail@email.com*)')
OR birthday_day REGEXP('(someemail@email.com*)')
OR birthday_year REGEXP('(someemail@email.com*)')
)
AND addressbook_id = 4
Neither are returning just where the content precisely equals atleast someemail@email.com. It's returning back everything with com or email or some. What changes do I need to make? There is a FULLTEXT index on the match columns.
Best would be to put the address in quotes and don't use a wildcard at all:
AGAINST ('"someemail@email.com"' IN BOOLEAN MODE)
Like that.
And I don't think you need the boolean mode for this.
Your fulltext search is not working because @ and . (and in fact most non-alphanumeric characters) are word delimiters. Therefore 1. e-mails get indexed as three separate words and 2. word delimiters are ignored from your search string.
Just create a regular multi-columns index on your columns, and search with a standard LIKE:
WHERE firstname LIKE 'someemail@email.com%' OR ...
This query will be able to use the index and the search will be very efficient.
Also, get rid of these REGEXP(). They are useless (redundant) and will kill your performances (no index can be used).