SitePoint Sponsor

User Tag List

Results 1 to 10 of 10
  1. #1
    SitePoint Addict
    Join Date
    Oct 2008
    Posts
    263
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Querying time & specific field size

    Based on the following scenario:

    I have a table with like 4 varchar and int attributes, and with 6 text attributes.
    The text fields might contain large text.

    So I was wondering, if I'm running a query which selects NONE of these text attributes, let's say I'm selecting the primary key of the table only, will those text attributes affect this query?? Does the overall size of the table and the overall size of specific attributes affects selecting other attributes???

    Thank you

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,022
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    if you are selecting only the PK, then you will use only the index, and therefore none of the other columns will affect the speed of the query
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Addict
    Join Date
    Oct 2008
    Posts
    263
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    if you are selecting only the PK, then you will use only the index, and therefore none of the other columns will affect the speed of the query
    What if I'm selecting one or all of the attributes OTHER than the text attributes???

    Let's say, p_id, p_fname, p_lname, without selecting for example p_bio, p_bio2, p_selfdescription, p_entrance_essay etc.. which are full text...

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,022
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    do you know how to do an EXPLAIN?
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Addict
    Join Date
    Oct 2008
    Posts
    263
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    do you know how to do an EXPLAIN?
    mmmmmmm

    Anyway..
    Assume the following table
    p_id int PK
    p_fname varchar(20)
    p_lname varchar(30)
    p_bio text
    p_bio2 text
    p_selfdescription text
    p_entrance_essay text

    All the text fields will be filled with large text.
    Now assume all these are filled, and I m running a query to select p_id, p_fname, and p_lname only, would the rest of the fields, which are not selected and are text, affect this query due to their size???

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,022
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by zeez View Post
    ... would the rest of the fields, which are not selected and are text, affect this query due to their size???
    depends

    take your query, put the word EXPLAIN in front of the word SELECT, and show the results
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Addict
    Join Date
    Oct 2008
    Posts
    263
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I will try it thank you...
    But it depends on what??

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,022
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by zeez View Post
    But it depends on what??
    mainly on the suitability, to the specific query you're trying to run, of indexes which might be defined on your table
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Addict
    Join Date
    Oct 2008
    Posts
    263
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I tried the EXPLAIN but what should I look for in its results??

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,022
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    you should look for filesort or something similar

    i'm not the EXPLAIN expert around here, but i do know that the guys who are are going to want to see your SHOW CREATE TABLE output as well as your EXPLAIN output
    r937.com | rudy.ca | 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
  •