Is that a good practice to put several longtext into a same table mysql?

Question

I am creating a mysql table which contain several longtext rows. I am expecting a lot of users enter a lot of texts. Should I split them into different table individually or just put them together in one table? I concern about the speed, will that affect the speed when I query the result, how about if I want to transfer the data on the future? I am using InnoDB, or should I use Myisam?

CREATE TABLE MyGuests (
  id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  diet longtext NOT NULL,
  run longtext NOT NULL,
  faith longtext,
  apple longtext
);

When we consider about feedback for ex. **diet** here its better to keep it in single table because keeping them in different table doesnt make any sense. But you can surely distinguish repeated data like **water intake** for which you can create different column. Answers to this question totally depends on content & your requirements in future. — Nagendra Nigade, Apr 16 '15 at 04:50
I concern about the speed, will that affect the speed when I query the result, how about if I want to transfer the data on the future? — conan, Apr 16 '15 at 04:57
I think it not gonna affect to your speed. Use indexes on required column preferably on `integer`. and about data transfer we have facilities like `data load` / bulk migration , so i dint think it will create problem in future. — Nagendra Nigade, Apr 16 '15 at 06:03
Don't worry about the performance until you have evidence that the performance is inadequate. Why do you think it's more efficient to break everything up into separate tables? Generally speaking it's not. — deceze, Apr 17 '15 at 07:24

Markus AO · Answer 1 · 2015-04-17T07:56:49.147

The main concern over speed you'd have with this database layout is if your query is a SELECT *, while the page only uses one of the fields. (Which is a very common performance degrader.) Also, if you intend to display multiple texts per page in a listing of available texts etc., you'd probably want to have a separate description column (that has a truncated version of the complete text if nothing else), and only fetch those instead of fetching the full text only to then truncate it in PHP.

If you intend to provide search functionality, you should definitely use fulltext indexes to keep your performance in the clear. If your MySQL version is 5.6.4 or later, you can use both InnoDB and MyISAM for full text search. Otherwise, only MyISAM provides that in earlier versions.

You also have a third choice between an all-in-one table and separate-tables-for-each, which might be the way of choice, presuming you may end up adding more text types in the future. That is:

Have a second table with a reference to the ID of the first table, a column (ENUM would be most efficient, but really a marginal concern as long as you index it) indicating the type of text (diet, run, etc.), and a single longtext column that contains the text.

Then you can effortlessly add more text types in the future without the hassle of more dramatic edits to your table layouts (or code), and it will also be simple to fetch only texts of a particular type. An indexed join that combines the main entry-table (which might also hold some relevant metadata like author id, entry date, etc.) and the texts shouldn't be a performance concern.

Is that a good practice to put several longtext into a same table mysql?

1 Answers1