Page 1 of 1

indexed table

Posted: Mon Mar 12, 2007 7:51 am
by phpandmedontmix
Hello,

I'm very new to php and databases and need to make a table for class.

My table is a glossary table. What is the best way to setup the table? I need to make it easy to search.

I have this so far:

Code: Select all

CREATE TABLE myglossary (
`id` int(11) unsigned NOT NULL auto_increment,
`word` text,
`deff` text,
`status` tinyint(1) default '1',
PRIMARY KEY ( `id` )
)
My classmate told me that I have to index my word and deffinition to make it searchable but I'm not sure how? Do I index both fields? What about my id field?

Thanks!

Posted: Mon Mar 12, 2007 9:24 am
by feyd
Text columns will need a FULLTEXT index.

http://dev.mysql.com/doc/refman/5.0/en/ ... earch.html

Posted: Mon Mar 12, 2007 10:07 am
by Begby
You probably don't need word to be text since the longest word is floccinaucinihilipilification, make it varchar(255) or replace 255 with whatever the maxlength you want for a word. That will search a lot quicker.

Also, if they are only looking up words and not searching by defnition, then you don't need to index the definition.

Posted: Mon Mar 12, 2007 10:18 am
by mikeq
You can also specify how many characters are included in the index.

If your field is 255 characters long it is unlikely that someone would type a lot of letters to search on, they may type 10? 20? characters, so just include that many in your index.

Code: Select all

CREATE INDEX part_of_word ON myglossary(word(10));
Would create an index on the first 10 characters on the field.

Create Index