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
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...
Take a look at ORDER BY
Adding index on a column speeds up search queries, but slows down inserting / deleting. However, first make your application work, and optimize afterwards.
It's worth reading http://use-the-index-luke.com/ - yes, you can index every column; it will rarely do any good, because you have to tune the indices for the queries your actually running.
If you want an index-assisted sorting on each column, you should create an index on each column.
Note that index scan is not necessarily faster: if you want all (or even a significant part) of the records returned, then the filesort will most probably be more efficient (unless your tables are really large in which case you don't want all records anyway).
Indexes will only help if you are using ORDER BY along with LIMIT.
indexes should be created based on the criteria, a simple example in your case would be you might want to search your table for items that are of a particular price. The index for that should be item name, item description and price.
I tend to plan the table so that i know what i'm likely to do and then create the indexes accordingly. So you might have a function that getsItemsBySaleDate() or getItemsCheaperThan() etc... both of these would have different indexes as both would search different columns within the table. I would suggest for now simply create an index for each column on the table.
I'd also add one for:
item_search => itemname, itemdescription, price
Sure, you should create one index for each sort criteria. Don't forget to define additional keys to indexes. e.g. for the price key you should add item name, so the items will be sorted not only by price, but then name (within the same priced group).
As others told already, be careful with the number of indexes: all the indexes must be updated upon each insert or update. Do you really want to sort items on description?
Why use an index
Indexes are used for two things.
For selection of items you need an index, because searching through all records is not an option.
However if you only ever select 100 items at a time, MySQL can easily sort those items in place without using an index.
So first put indexes on the items that are in your where and join clauses.
Then see how many items you select per query. If it's fewer than say 200, I would not bother with indexes for sorting.
Adding an index
CREATE INDEX index_name ON tbl_name (price)
See: http://dev.mysql.com/doc/refman/5.1/en/create-index.html
For all the options you can give an index.
Creating the table
My suggestion:
CREATE TABLE materials (
id integer not null autoincrement primary key,
name varchar not null,
description varchar not null,
stockdate date not null,
saledate date not null,
price decimal(10,2) not null,
/*my suggestion, put an index on all, but not on description*/
INDEX `i_name` (name),
INDEX `i_stockdate` (stockdate),
INDEX `i_saledate` (saledate),
INDEX `i_price` (price)) ENGINE = MyISAM;
If you select on the description in the where clause, then add a fulltext index on description.
CREATE FULLTEXT INDEX i_description ON materials (description);
If you only sort on description do not add an index, it's not worth it IMO.