questions regarding MySQL optimizations

[davidklonski]

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

[r937]

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

[davidklonski]

what's the difference betweent the join syntax and the way I wrote it?
Is there a difference in performance?

[r937]

the difference is most noticeable when you try to write an outer join

performance for the inner join is the same