SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Zealot
    Join Date
    Mar 2004
    Location
    united states
    Posts
    178
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    questions regarding MySQL optimizations

    Hello

    Could someone tell me how the following optimizations are handled by MySQL 5.0

    It is good practice to place the most limiting conditions in the where clause first. Does MySQL read the conditions top to bottom or bottom to top? Should the most limiting conditions come first after the 'where' or at the end of it?
    i.e, which is better in MySQL 5.0?

    Where <most limiting> AND <least limiting>
    Where <least limiting> AND <most limiting>

    I am using like '%xxx%' on a certain column quite frequently. The table that has that column has a lot of data that (most of it is unique). Should I consider placing an index on that column?

    If I have 2 tables with data, and an additional table is used to connect them (i.e. table 'Ownership' connects 'people' and 'pets'), does the order of the table in the following where clause matters?

    Which will be faster?

    People.ID = Ownership.OwnerID AND pets.ID = Ownership.PetID
    Ownership.OwnerID = People.ID AND Ownership.PetID = pets.ID
    People.ID = Ownership.OwnerID AND Ownership.PetID = pets.ID
    Ownership.OwnerID = People.ID AND pets.ID = Ownership.PetID

    does the order matter?

    thanks in advance

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,015
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    i cannot answer specific questions about how mysql works internally

    however, i can tell you that any database which uses an optimizer will take care of finding the most limiting and least limiting conditions no matter what sequence you code them in

    also, database optimizers will usually ignore an index on a column if you use LIKE '%xxx%'

    as far as your join conditions for the Ownership table are concerned, you should be writing your queries with JOIN syntax anyway

    and no, it does not matter if you say A=B or B=A
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Zealot
    Join Date
    Mar 2004
    Location
    united states
    Posts
    178
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    what's the difference betweent the join syntax and the way I wrote it?
    Is there a difference in performance?

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,015
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    the difference is most noticeable when you try to write an outer join

    performance for the inner join is the same
    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
  •