storing long text pieces

Question

I am coding an online notes web application, and want to have support for notes ranging from 200 characters to very long ones (~more than 10000 characters). These notes should be searchable.

• How can I store the text note in my database, will it slow down my application if I use the same table for all notes?
• Should I implement multiple types of notes (like short, long, very long, etc) and use different tables for them?
• Should I use files instead of database entries for notes as long as 5000 characters?
• How can I process a text search through these notes (in case I store them on different tables and/or files)?

Answer 1

Use one table, with one TEXT column. That's perfectly suitable, MySQL can handle that without breaking a sweat. Put a FULLTEXT index on it for medium-advanced searching capabilities.

Answer 2

10,000 characters isn't all that large for MySQL. You can use a TEXT column to store up to 2¹⁶ bytes. That's enough if your text is ASCII. If it's Unicode, then you can use a LONGTEXT field to store up to 2³² bytes per record.

Designing separate tables for different note lengths is, I think, a bad idea. It will make querying much more difficult.

MySQL also supports full text searching. If you create an ISAM table, you can create a FULLTEXT index on the notes field to speed up searches (quite a bit). Search for FULLTEXT in the manual page for CREATE TABLE for details.

Answer 3

• No
• No
• No
• Use full text search in mysql or just a like '%search%' which would be a bit slower.

This kind of size of document is a doddle to a modern database, just built it and dont worry about it.