Sorry, it's probably my bad english that is confusing. When I sayed inequality, I ment operators like <= or >=. In fact, 'a' is an integer' which is a foreign key on a primary key of another table and b is a timestamp.
So, if I understand you, making an index on both ('a', 'b') will be faster than two separate indices ?
But, if yes, does this index can be useful for a search on 'a' only ? Or do I need a separate index for this ?
I generally also have a ORDER clause on 'b'. I suppose that the index will be a good point for it too ?
And, last question, I also have time-consuming queries that are of the form :
SELECT .. FROM table WHERE 'a'=x AND 'c'=y AND 'b' >= z
where 'c' is an integer, but that is not a foreign key. Does it makes sense to create an additional multi-column index on ('a', 'b', 'c') ? Does the order of declaration of columns in the index creation makes a difference ? (for example ('a', 'c', 'b')) ? And is this index useful for a search on 'a' and 'b' only ?
I am sorry for bothering you with all those questions, but I tried to get information from the doc, but I have big difficulties understanding multi-column index stuff...
I have a question concerning index : suppose I have a table with fields 'a' and 'b' and that all requests perform WHERE clauses on 'a' field, and some requests also perform WHERE clauses on 'b' fields. What is the best approach for indexing strategy:
One index on 'a' and one on 'b'
One index on both columns 'a' and 'b'
A combination of both solutions ?
Could you clarify your question a bit? Are you saying your queries are predominantly
SELECT ... FROM table WHERE a = ?
With some queries that are
SELECT ... FROM table WHERE a = ? AND b = ?
Thanks,
Jonathan
Moving your reply to the list.
Assuming the data type you are using supports B-tree indexes, I can't think of any cases where inequality (specifically <> or !=) would use an index, so a single index on 'a' is what you are looking for.
However, if you are doing anything with equality (<, <=, =, >=, >) then you would wnat a multi-column index on (a,b), in that column order.
