SitePoint Sponsor

User Tag List

Results 1 to 10 of 10
  1. #1
    SitePoint Wizard
    Join Date
    May 2002
    Posts
    1,370
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    executing the SELECT

    I've debated the cardinality of a table relationship. Each unique rowid can select from one on up to 250 values, for a miximum of six times. (presently six col's for this in a table -- which assumes a one-to-many relation). Even if this is a many-to-many, it may be easier to manage out of the existing table (by drop menus, since I am limiting total # of results).

    Question:

    If a statement filters like so, will this Select by the zipcol first (a zip code) as a condition before searching all other six cols in the table. This is a memory question:

    SELECT name WHERE zipcol = "55098" AND ((col1 OR col2 OR col3 OR col4 OR col5 OR col6) = 10)

    Please forget about syntax, this is a table design question.

  2. #2
    SitePoint Addict danfran's Avatar
    Join Date
    Jan 2005
    Location
    New York City
    Posts
    244
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I think your question is more what is the execution plan algorithm of that query in my database system?

    The answer depends on your database system, index configuration and whether or not you specify HINTS. If you put all of those columns in a single [composite] index (starting with zipcol), SQL Server will simply traverse the index once and give you the results without doing a bookmark lookup. A little wasteful, IMO, but oh so fast. -All kinds of possibilities!

    If you're using SQL Server, you can view the execution plan in Query Analyzer w/ CTRL K

    I did a quick test (col=blah and x or y or z) on an index-less table and lo and behold a big, fat, ugly TABLE SCAN was performed.

    Every index takes up space on disk and in memory so, you have to decide where you can trade speed for memory usage.. You can index every single column in your database and make it Google-speed. If you're low on memory this is pointless. You'd be better off just indexing the ZipCol and let the database engine (more processor intensive) do the work.

    -Dan

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    this is a table design question?

    normalize the 6 columns into their own table, declare an index on the two columns (key and col), and the performance should be even way much better
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Wizard
    Join Date
    May 2002
    Posts
    1,370
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Doyou think this is okay for thousands of rows (index cols) ? Or should I try to place into a single column many-to-many table , from where I would need to limit to 6 total values per user; making these updateable by 6 drop menus.

    Would much be saved in the way of memory by doing this way as opposed to the index/6 col's? (approx 6 X's the rows, instead of 6 seperate cols)

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    i'm sorry, you totally lost me with your last post

    maybe you can give some actual examples of the table layouts you're contemplating
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Wizard
    Join Date
    May 2002
    Posts
    1,370
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I'm sorry, I'll try to make that clearer the tables aren't entirely laid out yet)

    Debating on two basic schemas;

    a) Placing the mentioned six columns either along with the same table having the zipcol. Or elese creating the six columns on a seperate table linked to a table containing a zip column [ either case there are six columns ] OR

    b) combining all values that would have gone into these six columns and place these values into a single col many-tomany table(actually two totals column if you count the row id). Merely transferring the six column values into six rows per user.

    Would b) consume less resources with a JOIN statement having SELECT originaltable.name WHERE originaltable.zipcol = "55098" AND manytomany.col=10 (on the many-to-many)

    OR would SELECT on a) consume less with SELECT name WHERE zipcol = "55098" AND ((col1 OR col2 OR col3 OR col4 OR col5 OR col6) = 10)

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    yes, b) is much better

    i don't see why you call it many-to-many (unless each of the cols is actually a foreign key to some 3rd table where the col is the primary key)

    and this design is definitely more efficient for querying

    those ORs would likely cause a table scan
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Wizard
    Join Date
    May 2002
    Posts
    1,370
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    thing is, I'm not sure how to do an update on many-to-many values, and limit the values to 6 total -- doing these from drop menus.

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    update, or insert?

    if there's a maximum, this must be enforced by the application logic

    but, i mean, you'd've had similar complexity, trying to decide whether to slot a new col into col4 or col5 or whatever the next free slot was, and what to do if all 6 are occupied

    setting a limit on 6 rows is similar
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    SitePoint Wizard
    Join Date
    May 2002
    Posts
    1,370
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I'm going in circles.

    those ORs would likely cause a table scan
    But this wouldn't be nearly so bad if I could force this first:
    WHERE zipcol = "55098" //Then have the SELECT search the 6 col's.

    note: there shouldn't be more than 100-200 rowid's per zip code. (ea having 6 columns- also, these 6 col's contain 1-3 numerical characters only)

    Doing an Update on 6 col's is much easier than 6 rows of a many-to-many table. First, it is kept to 6 seperate values automatically if updating out of seperate columns. There is no chance of ending up with more than 6 values as there is from a many-to-many table have infinite number of rows (in the event of multiple updates, for example).

    Updating 250 possible values out of 6 drop menus, I would need to account for the order of how the values appear consecutively within the many-to-many table [ value 1, 2, 3, 4, 5, 6 ]. Further, I've noticed that mysql tables don't always necessarily maintain their consecutive Insert order (numerically id's yes, occurances within the row order, no)

    That Table scan sounds like a lot of memory -- would this scan the entire table -- the actual listing order sequence in the SELECT doesn't matter?

    One thing I forgot to mention is that the results from the SELECT will appear in large directory structure ]so the OR would need to be used for all results.
    Last edited by datadriven; Feb 7, 2005 at 07:41.


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
  •