Not a DBA so pardon my ignorance...
I have a table with 20 fields of which 8 are foreign keys. Mysql has by default added an index for each foreign key. Table has about 100k rows. When retrieving the data some of the foreign keys are used and some are not. Also, I would like to add indexes to two more fields that are used frequently while querying.
My questions are -
1) In the following code should we just index the field "criteria" or should col1, col2 be created as a non-clustered index?
SELECT col1, col2 FROM table1 WHERE criteria
My understanding - If only the field "criteria" was indexed then the query would use the index to resolve which rows to retrieve. Post that it would do a full table scan to retrieve the relevant rows
2) Can I remove the indexes on the foreign keys and still have the foreign key constraints work?
3) 10 indexes on a table with 20 fields. Would that cause a performance issue during inserts?
4) The most optimal storing scheme for the indexes - BTREE, RTREE and HASH. I assumed BTREE for queries that have > or < and hash for the = queries. Is that the right way to go?
Any suggestions would be greatly appreciated.