nagalenoj Posted December 9, 2008 Share Posted December 9, 2008 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.., Quote Link to comment https://forums.phpfreaks.com/topic/136205-how-to-design-the-database/ Share on other sites More sharing options...
Maq Posted December 9, 2008 Share Posted December 9, 2008 Sorry, I'm not following what you're trying to do. Can you elaborate or give an example? Quote Link to comment https://forums.phpfreaks.com/topic/136205-how-to-design-the-database/#findComment-710601 Share on other sites More sharing options...
btherl Posted December 10, 2008 Share Posted December 10, 2008 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. Quote Link to comment https://forums.phpfreaks.com/topic/136205-how-to-design-the-database/#findComment-711051 Share on other sites More sharing options...
nagalenoj Posted December 10, 2008 Author Share Posted December 10, 2008 Yes, multi-column indexes will solve the problem, I think.. Thank you. Quote Link to comment https://forums.phpfreaks.com/topic/136205-how-to-design-the-database/#findComment-711073 Share on other sites More sharing options...
btherl Posted December 11, 2008 Share Posted December 11, 2008 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. Quote Link to comment https://forums.phpfreaks.com/topic/136205-how-to-design-the-database/#findComment-712265 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.