SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    SitePoint Enthusiast rad2004's Avatar
    Join Date
    Aug 2004
    Location
    UK
    Posts
    50
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    table design help

    Hello,

    I'm putting together a user table which will hold (among other fields) country, region, subregion

    What is better (faster)?
    Having separate columns for country / region / subregion and doing
    SELECT * FROM user WHERE country = 'GB' AND age BETWEEN X AND Y
    SELECT * FROM user WHERE country = 'GB' AND region = 'ENG' AND age BETWEEN X AND Y
    SELECT * FROM user WHERE country = 'GB' AND region = 'ENG' AND subregion = 'LND' AND age BETWEEN X AND Y

    OR

    Having 1 column with concatenated country/region/subregion and doing
    SELECT * FROM user WHERE geocode LIKE 'GB%' AND age BETWEEN X AND Y
    SELECT * FROM user WHERE geocode LIKE 'GBENG%' AND age BETWEEN X AND Y
    SELECT * FROM user WHERE geocode = 'GBENGLND' AND age BETWEEN X AND Y

    I'm leaning towards the latter because I would only need a single 2 column index (geocode, age) and all 3 queries would utilize both columns of the index ... whereas in the first example the index would be (country, region, subregion, age) and I'm thinking the DB might only use the 1st column of the index (country) and ignore the rest if I only searched country and age (1. and 4. column) ...

    As always, thanks for any advise

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,262
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    i have two different answers for you, choose whichever one you like

    1. load up some high volume tables, run some timing tests, pick your method

    2. premature optimization is the root of all performance misery, don't concatenate columns just to appease an optimizer that you haven't even given a chance to work on the real values
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •