speeding up searches with indexes

[misterx]

I want to use indexes for a table I need to search but I'm getting really confused as to how to do it. :confused:

The table is like so:


+-----------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+------------------+------+-----+---------+----------------+
| id | int(10) unsigned | | PRI | NULL | auto_increment |
| title | tinytext | | MUL | | |
| star | text | | | | |
| synopsis | text | | | | |
| label | tinytext | | | | |
| format | tinytext | | | | |
| price | float unsigned | | | 0 | |
| thumbnail | varchar(34) | | | | |
| fullSize | varchar(34) | | | | |
| genre | varchar(20) | | | | |
| subGenre | varchar(20) | | | | |
+-----------+------------------+------+-----+---------+----------------+


I only need to search title, label, star, genre, and subGenre. Should I be making one index for each of those columns?

Also, I've been told that MySQL can somehow rate the search results by how closely they match the search query. Does that involve indexes?

Thanks.

[Kiwi]

The basic syntax is:
ALTER TABLE <table name>
ADD INDEX(title)
I would also highly recommend that you normalise your table. Things like star, genre, sub-genre and format are going to be repeated throughout your database, so these should be shifted to separate tables with a numerical key. Select on an inhdexed numerical field are much, much faster than using text and you will avoid data entry errors that would render your searches useless.