MySQL query time grows exponentially when data in a TEXT column grows linearly

Question

We have a table that currently has a TEXT column, and the column is about 2000 characters long. We wanted to see how the performance of the queries that this column would select would look like if the average value is 5k, 10k, 20k, etc.

We installed an isolated test and found that as the TEXT column length increases linearly, the query time increases exponentially.

Does anyone have quick thoughts on why this might be. May provide more information, but fairly straightforward.

Answer 1

One reason for this may be that the TEXT and BLOB fields are not saved along with all other "regular" fields, so the database engine actually needs to pull them out of another area of ​​the disk.

We will need to see your request. Is it just a search in the ID field or are you looking in the TEXT field? In the latter case, when the average length of the saved text increases, the amount of data for the database also increases and grows exponentially.

Answer 2

You can select only those fields that you want to view with limit :

 SELECT field1, f2, f3 FROM table1 ORDER BY id LIMIT 0,30 

For the next 30 lines do

 SELECT field1, f2, f3 FROM table1 ORDER BY id LIMIT 30,30 

In any case, you can never read 10k lines at a time, this will greatly speed up the selection.

Answer 3

This is due to how much data mysql can read during the disk read cycle,
and how much data can be sent over the network in a data sending cycle

as the size of the data increases, there will be more overhead

• disk read cycle (mysql spent more time searching for records)
• data transfer (requires more cycles to transfer data over the network)

not all data is stored in memory, especially in text and blob,
mysql needs to find data from disk,
and transfer back to customers

in other words, the mysql index is fast,
because disk reading is not required

Answer 4

This is a very wild guess, but it may be a problem with a low level of implementation, MySql does not expect that you will get so much data at once to redistribute a larger block of memory for internal use and copy data from the old location to the new one and repeat it over and over as of how the data grows, this is the only thing that comes to my mind that can explain the query time, increasing exponentially, while the data grows linearly. Your decision is to limit the amount of data that you receive immediately.