Indexing mysql table for selects

Question

I'm looking to add some mysql indexes to a database table. Most of the queries are for selects. Would it be best to create a separate index for each column, or add an index for province, province and number, and name. Does it even make sense to index province since there are only about a dozen options?

select * from employees where province = 'ab' and number = 'v45g';
select * from employees where province = 'ab';

If the usage changed to more inserts should I remove all the indexes except for the number?

Answer 1

An index is a data structure that maps the values of a column into a fast searchable tree. This tree contains the index of rows which the DB can use to find rows fast. One thing to know, some DB engines read plus or minus a bunch of rows to take advantage of disk read ahead. So you may actually read 50 or 100 rows per index read, and not just one. Hence, if you access 30% of a table through an index, you may wind up reading all table data multiple times.

Rule of thumb: - index the more unique values, a tree with 2 branches and half of your table on either side is not too useful for narrowing down a search - use as few index as possible - use real world examples numbers as much as possible. Performance can change dynamically based on data or the whim of the DB engine, so it's very important to try and track how fast your queries are running consistently (ie: log this in case a query ever gets slow). But from this data you can add indexes without being blind

Okay, so there are multiple kinds of index, single and multiple column. You want multiple indexes when it makes sense for indexes to access each other, multiple columns typically when you are refining with a where clause. Think of the first as good when you want joins, or you have "or" conditions. The second is better when you have and conditions and successively filter rows.

In your case name does not make sense since like does not use index. city and number do make sense, probably as a multi-column index. Province could help as well as the last index.

So an index with these columns would likely help: (number,city,province)

Or try as well just: (number,city)

Answer 2

You should index fields that are searched upon and have high selectivity / cardinality. Indexes make writes slower. Other thing is that indexes can be added and dropped at any time so maybe you should let this for a later review of the database and optimization of querys. That being said one index that you can be sure to add is in the column that holds the name of a person. That's almost always used in searching.

According to MySQL documentation found here:

You can create multiple column indexes and the first column mentioned in the index declaration uses index when searched alone but not the others.

• Documentation also says that if you create a hash of the columns and save in another column and index the hashed column the search could be faster then multiple indexes.

  SELECT * FROM tbl_name WHERE hash_col=MD5(CONCAT(val1,val2)) AND col1=val1 AND col2=val2;

  • You could use an unique index on province.