Babra Cunningham Babra Cunningham - 1 month ago 6
MySQL Question

MySQL Innodb indexing confusion

A very basic database design question:

I'm creating a very large table with the following columns: Employee_Name(VARCHAR), Employee_ID(INT), Employee_Birthday(INT).

I wish to create a single column b-tree index for Employee_ID, as many of my queries use SELECT and WHERE clauses based on the values in this column.

Although I will not be updating the Employee_ID once created, I will be frequently INSERTING new employee's into this table.

Does the addition of rows with an indexed column create additional cost compared to no indexing? Or does the additional cost only apply when UPDATING existing rows?

Answer

When evaluating the creation of an index, the KEY question is: Would this table be accessed MOSTLY for queries or for INSERTIONS. Also, you need to specify what you mean by very large table (since you are talking about employees, I would guess that the table would host, perhaps, less than 100K records, right?). If you expect to have, say, thousands of INSERTs for each SELECT, you might try going without index. If, on the other hand, you will be mainly querying the table, setting an index in your column Employee_ID is the way to go.

Comments