0

Advantages? and why not just index fields separately? can any body explain me in simple terms.

James A Mohler
  • 11,060
  • 15
  • 46
  • 72
Abhimanyu
  • 4,752
  • 7
  • 33
  • 44
  • 1
    I think this is not the right place for these things: people post questions because probably they don't know something that for another one could be easy. A question not always has to _bring_ something to SO.. sometimes it brings a good answer to OP. Here people give thei free help and get helped for free when they need. Naturally, this is only my humble opinion... – Marco Oct 08 '11 at 11:03
  • @Abhimanyu, FYI , I am not the downvoter. – Bhaskar Oct 08 '11 at 11:03
  • @Abhimanyu: you can't ask people not to downvote you. You may ask the reason, but you have to accept other people's opinion... – Marco Oct 08 '11 at 11:06
  • Please, stop commenting with something that is not about the question, or I have to ask other to close this thread... – Marco Oct 08 '11 at 11:07

1 Answers1

1

If you have to query using a composite WHERE (say on two different fields) a composite index should be faster than having two single indexes...
This is a general answer, anyway you should analyze any single query...

Marco
  • 56,740
  • 14
  • 129
  • 152
  • @Abhimanyu: it's easy; when you declare an index you can choose multiple fields to be associated with that index. Then, when using a query that uses those fields in where clause, that index will be used. It's not a law, but normally it works... – Marco Oct 08 '11 at 10:44
  • the order of those field matters in composite indexing? why -1? – Abhimanyu Oct 08 '11 at 10:46
  • 1
    @Abhimanyu: I didn't give you -1, I preferred to answer... Anyway yes, the order is important. Take a look at this [link](http://askville.amazon.com/MYSQL-composite-index-fields-separately/AnswerViewer.do?requestId=718314) and at [this one](http://stackoverflow.com/questions/1823685/when-should-i-use-a-composite-index) – Marco Oct 08 '11 at 10:50
  • 1
    @Abhimanyu An index will normally be disqualified if a the first column of is not used. You may find the following resources worth a read [Tips on Optimizing SQL Server Composite Indexes](http://www.sql-server-performance.com/2007/composite-indexes/) and [Section "Composite Indexes" in SQL Server Indexes](http://odetocode.com/articles/70.aspx) (not MySQL but still valid). – jensgram Oct 08 '11 at 10:51