hello,

at first, I have declared the fields in my table like this:

...
title varchar(200) default NULL,
description text,
keywords text,
details text
....

But I have limited 'description' and 'keywords' to 300 characters each and 'details' to 1000 characters in my php script.

Now, shall I change it like this:
...
title varchar(200),
description varchar(300),
keywords varchar(300),
details varchar(1000),
....

I have a fulltext index on these fields and I wish to optimize my table for good performance.

please inform me how should I declare my fields.
thank you.

If you've set a character limit in the PHP fields within your script, then maxlength of characters inserted into your db will be ONLY what you have set in your php form fields as maxlength.

Example, if I have a field called `tomorrow` and set the maxlength in my form at 200 characters but I have defined the `tomorrow` field in my db as varchar(255) then the MAX data length that will be inserted into the field via my form will be *only* 200 characters (taken from my form maxlength) regardless of the amount of characters I have defined for field length in the db.

Generally, things like title, keywords, descriptions would all be varchar(255) and some field that would contain more information (larger amounts) like `details` would be set as text.

The maxlength of an index is 255.
You can add as many fields to an index as you want, as long as the length of field given to each (for the index only) doesn't equal more than 255.

Divide it up between the number of fields that you have and set each one to that number length.

Example, 5 fields in index using 255 maxlength on index means each field in the index can use a maxlength character set of 51 characters.

As long as the length of field is greater than 15 for each field added to the index, your index should be fine.

Are you using MyISAM?

In MYSQL prior to Ver 5 VARCHAR columns with a length greater than 255 are converted to the smallest text column type that will hold the data.
So VARCHAR(300) may well be converted to a TEXT type anyway.

For the "keywords" data, it would be a better idea to use a "normalised" structure to avoid massive duplication and wasted storage.

using an inverted index data storage as the model, you would have one table holding all the keywords and an ID number and a table holding the document IDs and the keyword IDs that go with the document.
You would then have a JOIN query to pull up the keywords for any given document.

It also makes it simple to do the reverse, and pull up all documents that are linked to a particular keyword.