Proper use of indexes

[ggeiger]

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.

[r937]

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

[ggeiger]

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.

[r937]

none of those columns will require a FULLTEXT index

since you must have a value for all four columns, you can use a single index --

ALTER TABLE members
ADD INDEX srch_ix ( name, email, birth_date, ship_state )

[ggeiger]

Thanks!

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.

[r937]

look up LIKE and wildcard characters

[ggeiger]

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'