Does indexing a longtext field help with text searches?

[benphp]

I have a search query that searches longtext for search terms, and the queries are starting to bog down. Would it help to index a text field that can contain 5000 chars or more?

 

Thanks!

 

 

[benphp]

While I was waiting, I did some testing. OMYGOD what have I been missing?

 

For anyone who hasn't done this, index your long text tables so:

 

ALTER TABLE `tablename` ADD FULLTEXT (textfield1);

ALTER TABLE `tablename` ADD FULLTEXT (textfield2);

 

then change your select statements from

 

WHERE textfield1 LIKE '%$term%'

 

to

 

WHERE MATCH (textfield1) AGAINST ('$term')

 

What a difference!

[benphp]

Okay, so can someone tell me why it's not a good idea to index everything?

[fenway]

Okay, so can someone tell me why it's not a good idea to index everything?

Because not every query can utilize every index (very true for 4.1) and it's expensive to keep it up to date.

[benphp]

Expensive in storage or time? If I understand correctly, it takes a little more time to enter a new record if a table is indexed, and the index itself takes disk space. Is that what you mean by expensive?

[fenway]

Both, but time is more important -- unless the table really grows, and then index performance drops with increasing size.