How to design the database

[nagalenoj]

Dear friends,

    I have a requirement to search based on all the fields in a table. How can I manage the index in the table.

 

For example, consider a table has,

name, id, class, sub-class, section, rank, priority and more.

 

I have to provide a option to search by all the the columns.

If I have to search by one column, I will set the index to the column for efficiency. But, here the requirement is to search by all the fields in the table.

 

Kindly help me in the above..,

[Maq]

Sorry, I'm not following what you're trying to do.  Can you elaborate or give an example? 

 

 

[btherl]

You can put one index on each column (eg, if you have 10 columns then you have 10 indexes).  This means that an insert or update must update all 10 indexes though, so insert and update will be much slower.

 

If you are using a recent version of postgres then multi-column searches can also take advantage of multiple indexes.

[nagalenoj]

Yes, multi-column indexes will solve the problem, I think..

 

Thank you.

[btherl]

I'm not sure if this is just the words, but it's not multi-column indexes I'm suggesting, it's multiple single-column indexes.

 

A multi-column index is like

 

CREATE INDEX tab_id_name_idx ON tab (id, name)

 

Multiple single column indexes

 

CREATE INDEX tab_id_idx ON tab (id)

CREATE INDEX tab_name_idx ON tab (name)

 

The multi-column index is usually only useful for queries involving id, but the two seperate indexes can also be used for queries on name, as well as queries on both name and id.