Decide on an appropriate indexing system

When a table is created an index is not required although an index can be amazingly useful in speeding up access the database, the only problem with a table index is that is slows down the database when adding or updating the contents. The best way to describe it is like a filing cabinet, if all your documents wee just thrown into the filing cabinet then it would take quite a time to retrieve the document that you are looking for, but by simply labeling each drawer of the filing cabinet with a letter and then organizing the files put into the filing cabinet by the first letter of the document and putting them in the appropriate drawer, when trying to find the document you are looking for the search is not going to take ever nearly as long, although actually putting the file into the filing cabinet in the first place is going to take slightly longer. It is the same with databases, by indexing the database although it takes longer to update the database it is quicker to access.

So what are we best off indexing by? the `Book name'? the `ISBN number'? Well in my opinion you are usually best off indexing by the field that is going to be searched the most, in fact you can index by several fields in MySQL, as long as the total length is no more than 250 characters, so lets have a look at each field that we have and how many characters they are going to take up in the index.

`Book name' could take as many as 255 characters, although it is probably going to be one of the most searched by, `ISBN number' is a bigint, which takes up 8 bytes as it is stored as a number and hence uses bits to store its value, this is usually a field that people are looking for although some people would like to search by `ISBN number' so I think that this should be included in the index. `Price' is a decimal/numeric and hence could be as many as 6 or 7 bytes, which is not very many in the main index. `Date purchased' is another field which someone may want to search by, and it takes up 8 bytes altogether, so lets include it in our index.

So we have now decided on which fields we want to include in our index and the total number of bytes is 255+8+7+8 which is greater than 250 so we need to cut down somewhere, as we are allowed to use sections of each field and considering that the `Book name' field is of variable length and hardly any books are actually going to use the whole length on the field I believe that this is where we should cut down the index size. So lets consider how much of the `Book name' field we want to index, the question is of course how much of the `Book name' field are people going to use, and I think that if we indexed the first 30 characters then we are going to cover almost all of the books that we put into the table, so for this example that's how many characters from that field I'm going to use.

In our index I suggest we use all the fields, but use only the first 30 characters from `Book name', which means our index is going to be about 54 bytes in size.

One more thing is that if any field is going to be used in any index then it must be defined as 'not null` - this is all in the MySQL manual if you have a look.