是否可以索引表的每一列？

Question

Say I have a table called materials

materials table contains columns

item name | item description | stock date | sale date| price |

what I am looking into is some times I may want sort result by item name and may be by item description and may be by stock date and may be by sale date and by price.

So how I design a table according to above criteria? And how do I add index to all columns? Is it necessary to add index to all?

Any help?

well my table will have more than a million rows

I am using PHP and MySQL

Answer 1

There's no reason why you can't have an index on every column if it will help. You have to bear in mind the consequences of indexing like slowing down inserts/deletes. You need to weigh up the pro's and con's.

To create index...

http://dev.mysql.com/doc/refman/5.0/en/create-index.html