indexes, foreign keys and optimization

Question

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.

Answer 1

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.

Answer 2

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

MySQL can only use one index per (sub)select.
Howover InnoDB can use covering indexes, so if you have a compound index on criteria+col1+col2 it will never have to read the actual table.

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

Correct provided the cardinality of the index is high enough.

2) Can I remove the indexes on the foreign keys and still have the foreign key constraints work?

That's crazy talk, a foreign key constraint is implemented using an index.
The index is the constraint.

3) 10 indexes on a table with 20 fields. Would that cause a performance issue during inserts?

Yes probably.
Besides MySQL will only ever use one index per selected table, so having individual indexes on the fields will not help you much if you list multiple fields in the where clause.
Try and group related fields in a compound index.
Every time you do an insert or a update the relevant indexes need to be rebuild.
MySQL does buffer this, so 1 insert does not mean 1 disk-write but indexes are definitely not free.

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?

InnoDB only allowes BTREE indexes: http://dev.mysql.com/doc/refman/5.0/en/innodb-physical-structure.html
A hash index is used only on memory tables, these are temp tables that die when the connection that created thenm is destroyed.
The hash keys are super for = comparisons, but they don't really resolve in O(1) time, MySQL allowes hash collisions: http://dev.mysql.com/doc/refman/5.5/en/index-btree-hash.html
R-TREE is used for spatial indexes, only MyISAM allowes these: http://dev.mysql.com/doc/refman/5.0/en/optimizing-spatial-analysis.html
If you are deeply into spatial data I'd recommend PostgreSQL over MySQL, it has much better support for GIS related data.

More reading:

Adaptive hash indexes: http://dev.mysql.com/doc/refman/5.0/en/innodb-adaptive-hash.html

Answer 3

I'm a generalist, not a MySql expert, but here goes...

1. You shouldn't need an index (in any database) on columns in the 'select' clause, just on the column(s) in the 'where' clause.

2. As far as I know, you cannot have foreign key constraints without the indexes. I believe that the constraints use the indexes.

3. There is always overhead for updating indexes on inserts, and of course, the more you have, the more overhead. 10 indexes probably won't be a problem, but you'd have to try it in your particular situation, with realistic amounts of data, and measure to find out for sure.

4. Sounds right, but I'm not sure.