The database is for members who have subscribed to a service. I’d like to search the DB based on the user entry of a few fields, check if the member already exists, then return a set and let the user click their name (if found).
I believe I will need to use indexes here as the table will grow pretty large (between 200,000 and 500,000 records). My question is, what indexes will work best for each column? The fields the user will enter values for are:
NAME (varchar)
EMAIL (varchar)
BIRTH DATE (date)
STATE (varchar)
The SELECT query would search the DB looking for a match. Your help is much appreciated, thanks.
a lot depends on whether the search query will always require a search value for each column, or whether the user interface will allow the person searching to leave one or more fields blank, with the idea that a blank search field means that all values of that column are acceptable
you should probably get the search query working correctly before worrying about optimizing the database performance
that should only take a few days, eh
at worst you will have a half dozen indexes, and you can create them in about two minutes, so feel free to ask again when you have the search working
Thanks for the good advice. This case requires all fields or the form will not send. The working query I put together is follows:
SELECT
member_id, name, birth_date, email, ship_state
FROM members
WHERE
MATCH (name) AGAINST ('$name')
AND email='$email'
AND birth_date='$birth_date'
AND ship_state='$state'
I realize that the NAME field will need a FULLTEXT index. How about the others? DO they need to be indexed at all? Again, thanks for the help here.
I figured the NAME column would need a FULLTEXT index since the user might have typed something like “Freddy Prince, Jr.” the first time, and only “Freddy Prince” when searching the database, and I’d want to return the record if that were the case.
I’m getting that confused feeling, so let me know if I’ve got it wrong.
Gotcha. For anyone following along, I added the index mentioned above by r937 to the database, and my query (for my application) looks like this:
SELECT
member_id, name, birth_date, email, ship_state
FROM
members
WHERE
name LIKE '%$name%'
AND email='$email'
AND birth_date='$birth_date'
AND ship_state='$ship_state'