Mysql – indexes, foreign keys and optimization

database-designMySQL

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.

Best Answer

  1. In the example you show, the expression would use the index on criteria to resolve which rows to retrieve, and the leaf nodes in the index contain references to the corresponding rows, either by physical location in the case of MyISAM or by primary key value in the case of InnoDB. That way it avoids a full table scan.

    Note that if the query optimizer thinks your query matches too much of the table, it will ignore the index, because it's just as fast to do the table-scan. By analogy, this is why no one bothers to put the word "the" in the index at the back of a book.

  2. No, MySQL requires that an index exists for the foreign key. If an index already exists, InnoDB will use it, but it will create one if necessary. I have found some corner cases where you can trick InnoDB by dropping the index after defining the foreign key constraint, but this would not be advisable, because the index is important for checking referential integrity quickly.

  3. It's true that insert/update/delete needs to update indexes to reflect values added or removed. But it's not as bad as you might fear. Also, InnoDB implements a "change buffer" which records how your update affects secondary indexes, and reconciles it with the whole index later.

  4. InnoDB and MyISAM support only BTREE indexes. InnoDB also has an internal feature called the adaptive hash which does do a hash lookup for frequently-requested index entries. But this is completely automatic and transparent; you don't have to define any index or tune any config parameters.

Related Question