Storing TEXT fields in large volumes in Mysql

There is a Mysql server with a table of contents of the articles.

Structure: id (int), article_id (int), content (text)
About 1.5 million records. The volume table is 5Gb. The total amount of data in Mysql 9Gb

Recently the server became slower
Does it make sense to make this data in MongoDB to reduce the size of the Mysql performance
October 8th 19 at 02:01
5 answers
October 8th 19 at 02:03
9гб even for mysql is a small value. Look for a problem in another. For example, when data were few, it was noticeable the lack of an index on some more or less frequently used query.
Thank you! - Marianna commented on October 8th 19 at 02:06
October 8th 19 at 02:05
Not quite clear the original data. In addition to querying more importantly, are the PRIMARY indices.
To reduce the occupied disk space when clubsarahjean CPU, you can use the packaging text on the fly using COMPRESS/UNCOMPRESS.
October 8th 19 at 02:07
Makes sense sometimes to make the text field in a separate plate if twitch data from this table without this field.
Why? Text's and BLOB s and so are stored separately. - Marianna commented on October 8th 19 at 02:10
Stored separately, but this action helps with large volumes of data - Ignacio_Romaguera commented on October 8th 19 at 02:13
October 8th 19 at 02:09
To store text articles in a database is silly — better to keep the link or to communicate directly with the room key. This approach you'll reduce the load on the database and increase the responsiveness of the server, because you can organize casiraya at the server level and PS. To search you need to use Sphinx.

Can kill...
In short, it is nonsense, as the database stores data in the same FS, only, usually, more efficiently. - Marianna commented on October 8th 19 at 02:12
Yes, a DBMS stores data in the same file, but:
— DBMS excess broker;
Table with TEXT or BLOB fields cannot be fixed;
Caching DBMS is very poorly behaved when working with large samples.

A single critical advantage of a DBMS is search. But if Sphinx is used, and it's not a problem. - Ignacio_Romaguera commented on October 8th 19 at 02:15
Text/blob is stored separately from the strings themselves, so should not affect the sampling rate. But the queries that use temporary tables with the participation of these types are created on disk, not in memory, because the memory engine does not support these types. But judging by the structure of the table, there are only requests for the index must be on speed should not affect.

And, some contradiction, no? — if "the advantage of a DBMS is a search", why the Sphinx? :) Just did a full text search in mysql is one of the biggest disadvantages. - aniya.Kertzmann65 commented on October 8th 19 at 02:18
"The Text/blob is stored separately from the strings themselves, so should not affect the sampling rate. "
— Really? In my opinion you are mistaken.

"But judging by the structure of the table, there are only requests for the index must be on speed should not affect."
— It is highly unlikely that this is the full table structure.

"if "the advantage of a DBMS is a search", why the Sphinx?"
— Some people fear third-party solutions. - antonette.Gislas commented on October 8th 19 at 02:21
Hmm, well it's common knowledge. 10.5. Data Type Storage Requirements: [...] For BLOB and TEXT data, the information is stored internally in a different area of memory than the row buffer. [...]

Well, if this is not the full structure of the table, and to talk about.

I mean, the Sphinx in this particular case should not be needed. - eloise_Hahn commented on October 8th 19 at 02:24
"Hmm, well it's common knowledge. 10.5. Data Type Storage Requirements: [...] For BLOB and TEXT data, the information is stored internally in a different area of memory than the row buffer. [...]"
— I do not know. I'm talking about what DBMS it is easier to return more bytes than tens of kilobytes. - Frederick5 commented on October 8th 19 at 02:27
So if you need text — you its still to read. If you don't need it and the base to read it from disk will not. - eloise_Hahn commented on October 8th 19 at 02:30
October 8th 19 at 02:11
So maybe just base has grown so much that the indices did not fit in key_buffer and indexes now muscle just stopped usfsa?

Find more questions by tags HighloadMongoDBMySQL