MySQL Index Structure: multiple or single index?

Question

Hi, I have question about database optimizing, indexing. I have table that called "projects" and i will execute queries like this:

Ordering Queries

SELECT * FROM projects WHERE active = 1 ORDER BY created

SELECT * FROM projects WHERE active = 1 ORDER BY project_deadtime

SELECT * FROM projects WHERE active = 1 ORDER BY project_allowedtime

My Table Stucture Like This

id int(11) NO PRI NULL auto_increment employer_id int(11) NO MUL NULL
project_title varchar(100) NO MUL NULL
project_description text NO NULL
project_budget int(11) NO NULL
project_allowedtime int(11) NO NULL
project_deadtime date NO NULL
created datetime NO MUL NULL
active tinyint(1) NO MUL NULL

Which columns should i create index and how(single or multiple column index ?). For example should i use active-created & active-project_deadtime & active-project_allowedtime multiple indexes or single active index is enough ? Thanks

EDIT: projects table will have maximum 1000-2000 rows. SELECT queries performance is important and about %90 of projects is active.

Answer 1

You need to consider the selectivity of the active index.

• If a very small number of your projects are active at any time:

  (active)
  

• If only a relatively small number of your projects are active at any time but the table is very large:

  (active, created)
  (active, project_deadtime)
  (active, project_allowedtime)
  

• If a large proportion of projects can be active at the same time:

  (created)
  (project_deadtime)
  (project_allowedtime)
  

Update: based on the new information you provided, I would go with the last option. Though with such a small table the sorting should be close to instant even without an index. Another alternative is to modify the third option to be a covering index.

Answer 2

Which queries will be used most often? How many rows will there be? Which queries need to be fastest? Do you run more SELECTs or more INSERTs? There are a lot of considerations in tuning performance of a database.

Based only on what you've posted, you're using the following columns only:

1. active
2. created
3. project_deadtime
4. project_allowedtime

An index solely on active would do little good — it would probably narrow the results down to about half.

The other columns are each potential indices. If you're not as concerned with the performance of INSERTs as the performance of SELECTs, I'd say index all three, giving priority to the column likely to narrow down a query to the fewest rows:

1. (created, active)
2. (project_deadtime, active)
3. (project_allowedtime, active)

This will allow MySQL to use the index if only the first column is used (e.g., if only created is needed) or if both columns are used (to narrow results even further).