If my query has an ORDER BY clause that spans multiple tables, then what kind of table indices should I use to enhance query speed?
For instance: SELECT ....... ORDER BY Table1.Field1, Table2.Field2, Table3.Field3
Conventional wisdom is that if you have ORDER BY Table1.Field1, then you need an index in Table1 that sorts Field1. If you have ORDER BY Table1.Field1 DESC, then your index needs to sort Field1 descendingly. If you have ORDER BY Table1.Field1, Table1.Field2, then you need a two-field index in Table1 that sorts Field1, Field2 -- a single-field index for Field1 won't help here (so tricky). So what should I do when ORDER BY spans multiple tables?
Same question for WHERE clauses. If I have WHERE Table1.Field1=100 AND Table2.Field2=200 OR Table3.Field3=300, what indices would benefit the performance? Do AND and OR play a role in this?