sql - Indexing mysql table for selects -

i'm looking add mysql indexes database table. of queries selects. best create separate index each column, or add index province, province , number, , name. make sense index province since there dozen options?

select * employees province = 'ab' , number = 'v45g'; select * employees province = 'ab'; 

if usage changed more inserts should remove indexes except number?

an index data structure maps values of column fast searchable tree. tree contains index of rows db can use find rows fast. 1 thing know, db engines read plus or minus bunch of rows take advantage of disk read ahead. may read 50 or 100 rows per index read, , not one. hence, if access 30% of table through index, may wind reading table data multiple times.

rule of thumb: - index more unique values, tree 2 branches , half of table on either side not useful narrowing down search - use few index possible - use real world examples numbers as possible. performance can change dynamically based on data or whim of db engine, it's important try , track how fast queries running consistently (ie: log in case query ever gets slow). data can add indexes without being blind

okay, there multiple kinds of index, single , multiple column. want multiple indexes when makes sense indexes access each other, multiple columns typically when refining clause. think of first when want joins, or have "or" conditions. second better when have , conditions , successively filter rows.

in case name not make sense since not use index. city , number make sense, multi-column index. province last index.

so index these columns help: (number,city,province)

or try just: (number,city)