Can anyone give me a good explination/idea of where to use indexing for maximum benefit. I've been looking into it a little and turned up not a lot so far.
My database is similar to a forum with tables; members, member posts, stats and suchlike. I've created all my tables with an auto incriment ID field at the start which I've been setting as the primary index purely because I was told you should always have an index somewhere and this is usually the best place.
I'm still not sure exactly of the nature of indexes and the different types and I read somewhere they can slow down INSERT statements if not used wisely.
I'm currently working on a table to log member posts, where they posted and when so that all their posts in different locations can be referenced easily. I seemed to make more sense than querying all the different tables for user id 'x' that may or may not contain posts from them. My plan is to log each post on this table with the member id that posted it, and the table and id of the corresponding post. Is that a good idea? :)
And of course, how best to index it?
Any thoughts more than welcome