SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Zealot
    Join Date
    Feb 2008
    Posts
    165
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Proper use of indexes

    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.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,338
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    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

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Zealot
    Join Date
    Feb 2008
    Posts
    165
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for the good advice. This case requires all fields or the form will not send. The working query I put together is follows:
    Code MySQL:
    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.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,338
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    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 )
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Zealot
    Join Date
    Feb 2008
    Posts
    165
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,338
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    look up LIKE and wildcard characters
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Zealot
    Join Date
    Feb 2008
    Posts
    165
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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:

    Code MySQL:
    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'


Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •