1. for example, in my query I have "WHERE sIndex.doc = sDocs.id", where sIndex doc is indexed, and sDocs.id is primary key. Does the order matter? If I put it as "WHERE sDocs.id = sIndex.doc", would it be faster? Or slower?
Shouldn't make a significant difference. In terms of performance, it's generally better to join the smaller table to the larger one. MySQL uses its internal statistics to decide how to do this, so apart from exceptional cases, it will decide the best way to do it.
2. Shouldn't I drop certain indexes? Say, if I drop sIndex.doc index, would the previous WHERE become much slower, in first or second variation?
Probably. You don't need indexes that you don't search on or join on. If a field doesn't appear in the Where clause of a query, it doesn't need to be indexed.
As well as using up disk space, surplus indexes slow down inserts, so you should define as many indexes as you need, but no more.
3. sTags table is very small (tens of records). Is there reason to keep sIndex.tag (reference to sTags.id) indexed? Or should I drop this index?
Well, the index is not going to help much help in searches/sorts, but it's also not using much disk/memory, so it's not doing any harm. If it's enforcing a unique requirement, I'd leave it there.
4. Shouldn't I create multifield index instead of a number of single-column ones (I believe not, as the fields are totally unrelated)?
Depends totally on the circumstances. If you perform multifield searches/sorts (e.g. "order by field1, field2, field3"), then multifield indexes will help. However, in this example if you have an index defined for "field1, field2, field3" and you "order by field2", the index will be no help at all.
Best of luck,
James
