SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Evangelist
    Join Date
    Jan 2005
    Location
    UK
    Posts
    539
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Correct index to use

    I'm experiencing high server load due to mysql.
    I have lots of these queries:

    SELECT * FROM tablea WHERE field1='XXX' AND field2='YYY' ORDER by timeset DESC LIMIT 1;

    I'm retrieving around another 5 fields as a result.
    Should i create a multi column index on tablea setting all 3 fields?

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,340
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by skyline View Post
    I'm retrieving around another 5 fields as a result.
    how can that be???

    you're already retrieving all possible columns via your use of the dreaded, evil "select star"


    Quote Originally Posted by skyline View Post
    Should i create a multi column index on tablea setting all 3 fields?
    that depends
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Evangelist
    Join Date
    Jan 2005
    Location
    UK
    Posts
    539
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quiet right! So i need to list all the fields after the select. When you say it depends....?

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,340
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by skyline View Post
    Quiet right!
    quiet riot!!! one of da best metal bands evar!!

    Quote Originally Posted by skyline View Post
    When you say it depends....?
    yup, it depends on which queries you want to run
    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
  •