On Thu, Sep 26, 2002 at 03:01:35PM +0530, Shridhar Daithankar wrote:
Content-Description: Mail message body
> The index creation query was
>
> CREATE INDEX index1 ON tablename (esn,min,datetime);
>
> What if I put datetime ahead? It's likely the the datetime field will have high
> degree of locality being log data..
The order of fields depends on what you're using it for. For example, you
can use the above index for a query using the conditions:
esn = 'aaa'
esn = 'bbb' and min = 'xxx'
but not for queries with only
datetime = '2002-09-26'
min = 'ddd' and datetime = '2002-10-02'
The fields can only be used left to right. This is where a single
multicolumn index differs from multiple indexes of different columns.
Have you used EXPLAIN ANALYSE to determine whether your indexes are being
used optimally?
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/
> There are 10 kinds of people in the world, those that can do binary
> arithmetic and those that can't.
