SitePoint Sponsor |
|
User Tag List
Results 1 to 4 of 4
-
Apr 29, 2004, 04:54 #1
- 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
-
Apr 29, 2004, 05:13 #2
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 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
-
Apr 29, 2004, 05:25 #3
- 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?
-
Apr 29, 2004, 05:27 #4
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
the difference is most noticeable when you try to write an outer join
performance for the inner join is the same
Bookmarks