Indexing Question

[fishnyc22]

Hey everyone, I have a question I was hoping you guys can help me out with.

I have a history table that tracks the changes users make to their profile. It is basically a replica structure of my "profile" table with some slight changes. It writes a line for each user change to the profile. (Note: They can have several profiles. )

I have a history table. with the following columns.
(id) | (profID) | (uID) ...|...|...

(id) is Auto-Incremented and is Primary Key(I belive it has to be PK), the next column (profID) is the profile ID, The 3rd col is the user ID.

So if userID 5 changes their profile #2 twelve times over the course of a month, there will be 12 entries for prof(ID) thoughout the table.

This table will be used for analytics only not for user queries.

So I want to INDEX this table based profID right? or would it be uID > profID? Also, do I need to remove the (id) column from the table? If it is Primary Key will it screw up my index? I see it listed in the INDEX section of phpMYAdmin.

I'm new to this kind of stuff (indexing for large DBs).

Thanks for any guidance!

[guelphdad]

If you are searching for profid then yes you would want to index that column. you don't need to remove the auto increment column, all indexes act independent of one another and only one index is used at a time.

[raf]

which columns need indexing, depends on the querys you use them in. for the typical use of such a factstable, i'd recommend indexing each column that is used to join with its dimensiontable.
This table will be used for analytics only not for user queries.
not trying to complicate things, but if this is true, then you probably don't need an index since responsetime will not be critical and the frequency that you do these analysis might be very low (once a week/month?). building an index + indexing new records consumes runtime resources, so they will have some effect on your applications performance, without any real gain...
i probably wouldn't even run the analysis on a live db --> i'd dump the table and relevant dimensiontables in a csv, load in on an offline server and then create the indexes (after the tables are populated!). this way, the performance of your application wount be impacted + you'll have a clean cutof for your data.