Is fulltext index usuful for a few thousands of rows

lpa

I have a fulltext index in table where users private messages are stored. I created a fultext index because users can search their private messages.

Table size now is 257 MB but index size 133 MB!

I don't know if it's a good idea to keep this fulltext index if it takes so much space.

Maybe I can just create a normal index on "userid" column and than run the search query like this:

$result=mysql_query("SELECT id FROM messages 
WHERE userid='$id' and text LIKE '%$searchword%'") or die('Database error');

Each user has maximum 3000 messages and even if mailbox is 100% full the query will scan not more than 3000 rows.

What do you think about it? What is better - to keep fulltext index which eats space or to remove this index and scan rows without index?

Thanks.

Weedpacket

133MB is "too much space"? Maybe ten years ago it might have been considered too much space, but these days my phone has more storage than that.

text LIKE '%$searchword%'"

You could do that but it would be a lot less flexible and probably a fair bit slower, increasing server load in the process.

So basically you have to decide which is more valuable: your users' time or thirty cents' worth of disk space (assuming a 1TB drive for US$260).

lpa

thank you Weedpacket. I will think about it.

Of course, free space is not a problem. At the same time there are only a few users who use search function in their mailboxes. Most of users have some 10-20 messages and there is no need for searching. I was just thinking if it's usefult to keep such a fuge index if it's being used only sometimes.