I've got these three here columns on a table: year, pid (integer), and code (varchar). Want to index each one. Which is better (I'm using postgres, but I'm curious about systems in general): go all CREATE INDEX myidx ON mytable USING btree (year, pid, code); or go btree individual index on each three columns?
What are the implications of these two approaches?
The difference between them is as follows. Assume you have columns A, B, C and you put one complex index on them in exactly this order. now, when you do
SELECT * from table where A = .. AND B = .. AND C = then the index will be used and this is the most efficient usage of this index.
if you have query
SELECT * from table where A = .. then the index will still be used. However, the idnex will not (updated thanks to comment: or only partially for 2nd and 3rd example) be used in:
SELECT * from table where B .. SELECT * from table where A = .. AND C = .. AND B = .. SELECT * from table where A = .. AND C = .. however again will be used in:
SELECT * from table where A = .. AND B = .. hope this helps. The order is the KEY here. And of course what you need. If you have three queries like
SELECT * ... where A SELECT * ... where B SELECT * ... where C then of course make 3 indexes on single column. however if you have queries with AND, first make sure they have then same order and then go ahead and make 1 index for 3 columns
Don't create indexes on random columns, always create them according to the way you use a table. I.e. if you will be using a query like SELECT ... WHERE year = 2011 AND pid = 2, create an index on (year, pid). An index allows you to execute some queries more efficiently, but it also takes up disk space and makes inserts slower.
You can get a general idea of what indexes are good by reading 3 ways MySQL uses indexes and Indexes in MySQL (two short blog posts explaining how MySQL handles indexes). Just remember that each DBMS will work a bit differently.
Which is better
The only valid answer is - it depends (on your usage scenarios, amount of stored data, column selectivity, ...).
