Re: Index on multiple columns VS multiple index

Hello Jonathan,

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...

Thanks for your help,

Brice

2014/1/2 Jonathan S. Katz <jonathan(dot)katz(at)excoventures(dot)com>

> Hi Brice,
>
> On Jan 2, 2014, at 2:24 PM, Brice André wrote:
>
> Yes, except that on b, it's a inequality operator.
>
>
> 2014/1/2 Jonathan S. Katz <jonathan(dot)katz(at)excoventures(dot)com>
>
>> On Jan 2, 2014, at 2:17 PM, Brice André wrote:
>>
>> Hello everyone,
>>
>> 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.
>
> Best,
>
> Jonathan
>
>