SitePoint Sponsor

User Tag List

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

    table field order

    For SELECT queries, and how they are executing on the talbe fields, what takes precedence:

    The order the fields appear in the SELECT ?

    OR

    The order the fields appear in the table?

  2. #2
    SitePoint Member
    Join Date
    Feb 2005
    Posts
    1
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    The order the fields appear in the SELECT is correct.
    But if you put " SELECT * " you will get the order the fields appear in the table.

    www.easywebtech.com

  3. #3
    SitePoint Wizard
    Join Date
    May 2002
    Posts
    1,370
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    For heavily queried tables (without col indexes created, other than the primary id; since there are many inserts on these tables and I don't care to continually rebuild the index) - I'm debating on moving values out (larger memory required values) to other tables since these may be searched on for other unrelated queries.

    Are only the columns searched that are named within the SELECT?

    Can't other fields, those not mentioned in the SELECT be searches on as well. (like the event of a table scan)

  4. #4
    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)
    Quote Originally Posted by datadriven
    Are only the columns searched that are named within the SELECT?
    of course not

    example: who bought anything over a thousand bucks from our store

    select distinct custname from salestable where salesamt > 1000
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Wizard
    Join Date
    May 2002
    Posts
    1,370
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    - you are saying that cols other than 'distinct custname' and 'salesamt' would be searched on 'salestable' in your example?

  6. #6
    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)
    no, i'm not saying that

    i'm saying "the columns that are named within the SELECT" are not the only ones searched

    specifically, the columns named within the WHERE are searched

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

  7. #7
    SitePoint Wizard
    Join Date
    May 2002
    Posts
    1,370
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    By "other than" I meant 'in addition to', but I see why it was unclear.

    You see the term "full table scan" being used. Obviously this is the whole table.

    But is it the whole table only for columns mentioned in the query, as you've laid out here (assuming that it happens)? Certainly not the entire table, all columns.

  8. #8
    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)
    no, the "full" in "full table scan" refers to all rows

    actually, it's redundant

    a table scan by definition scans all rows
    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
  •