How to set the right indexes on a sql table?

Question

How can I identify the indexes that are worth to set on a sql table?

Take the following as an example:

select * 
from products 
where name = 'car' 
  and type = 'vehicle' 
  and availability > 3 
  and insertion_date > '2015-10-10' 
order by price asc 
limit 1

Imagine a database with a few million entries. Would there be benefits if I set an index on the combination of all attributes that occur in the WHERE and ORDER BY clause?

For the example:

create index i_my_idx on products
   (name, type, availability, insertion_date, price)

Answer 1

There are a few rules of thumb that can be useful when deciding which columns to index:

1. Make sure there's a unique index on the primary key - this is done automatically when you specify a PK in most RDBMSs including postgresql.
2. Add indexes for each foreign key. These are created automatically in some RDBMSs when you specify a FK but not in postgresql.
3. If a PK is a compound key, consider adding indexes on each FK making up the PK (except for the first, which is covered by the PK index). As in 2, some RDBMSs (e.g. MySQL with ISAM) add these indexes automatically when the FKs are specified.

Usually, but not always, table joins in queries will be PF to FK and by having indexes on both keys, the query optimizer of the RDBMS has flexibility in determining the optimum plan for maximum performance. This won't always be the best though, and experienced programmers will often format the SQL for a database query to influence the execution plan for best performance, or decide to omit indexes they know are not needed. It's worth noting that an SQL query that is optimal on one RDBMS is not necessarily optimal on another, or on future versions of the DB server, or as the database grows. The latter is important as in some RDBMSs such as postgres and Oracle, the query execution plans are dependent on the data in the tables (this is known as cost-based optimisation).

Once you've got these out of the way a lot comes down to experience and a knowledge of your data, and importantly, how the data is going to be accessed.

Generally you will be looking to index those columns which are best at filtering the data. In your query above, the obvious one is name. This might be enough to make that query run fast enough (unless all your products are cars).

Other than that it's worth making a list of the common ways the data is likely to be accessed e.g.

1. Get a list of products that are in a category - an index on category will probably help
2. However, get a list of products that are currently available - an index on availability will probably not help because a large proportion of products are likely to satisfy this condition.

Unless you are dealing with large amounts of data this can often be all you need to do, and it's not generally a good idea to add indexes "just in case" as there are overheads in maintaining them. But if your system does has performance issues, then it's worth considering how combinations of columns are being used in queries, reading up about the postgres query optimizer etc.

And to answer your last question - possibly, but it's far from the first thing to consider.

Answer 2

Well the way you are setting indexes is absolutely correct. Indexes has nothing to do with order by clause.

Some important points while designing SQL query

1. Always put the condition first in WHERE clause which will filter maximum rows for eg above query name ='car' will filter maximum records in products.

2. Do not use ">=" use ">" only because greater or equal to will always end up in checking greater first if failed equals as well which will reduce performance of query.

3. Create a single index in same order your where clause is arranged in.

4. Try minimizing IN clause use ANY instead.

Thanks Anant