#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2012
    Posts
    51
    Rep Power
    3

    best way to design mysql database table so that columns can be searched quickly


    Hi everyone
    Can someone please explain to me how MySQL searches through a database of multiple tables?
    The reason for the question is that I am trying to determine the best way to design A database that would allow me to index many searchable columns within the tables.
    It might be best if I show you what I mean;
    I have a user entity that has many categories like;
    1. Gender
    2. countryLocation
    3. LocationPostCode
    4. Main language spoken
    5. Country visited
    6. Available date
    7. Smoker
    8. Driver

    ETC ETC
    I expect to have millions of registered USERS s and will ALLOW members to search the database by one or many of the above categories..
    QUESTION
    Is it best therefore to keep all these columns in one table and then index each of them?. I however read somewhere that indexing several columns on a table affects performance .
    Alternatively, should I make separate tables for EACH of these main items and then just have the user_ id indexed in each of these tables. (i.e MySQL would then make a separate FETCH to each of these table to fetch all the Id in each table).
    I understand however that JOIN are a costly operation for MySQL to perform and that its quicker for it to work from one table.
    I would be very grateful if someone could explain the right approach and also explain how the engine conducts the search. I seem to be getting contrary information from many different sources.
    Warm regards
    Andreea
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,280
    Rep Power
    4279
    your first step, before indexing, is to normalize the data

    when that's done, come back and show us your normalized tables and then we'll talk about indexes
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2012
    Posts
    51
    Rep Power
    3
    Originally Posted by r937
    your first step, before indexing, is to normalize the data

    when that's done, come back and show us your normalized tables and then we'll talk about indexes
    Hello r937

    thank you so much for your reply. i will start normalizing it now and get straight back to you.

    warm regards

    Andreea

IMN logo majestic logo threadwatch logo seochat tools logo