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

We have a table that currently has a TEXT column and the length of the column averages at about 2,000 characters. We wanted to see what the performance of queries that select that column would be if the average was 5k, 10k, 20k etc.

We set up an isolated test and found that as the length of the TEXT column increased linearly, the query 开发者_开发知识库time increased exponentially.

Anyone have any quick thoughts on why this might be. Can provide more info but pretty straight forward.

---

One of the reasons for that could be because TEXT and BLOB fields are not stored alongside with all other 'regular' fields, so that database engine actually needs to pull these from another area of disk.

We'd need to see your query Is it just a lookup by ID field, or do you search in TEXT field? In the latter case as average length of stored text increases, so does the amount of data for the database to process and it grows exponentially.

---

You can select only these fields that you want to view using limit:

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

For the next 30 rows do

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

You can never read 10k rows in one go anyway, this will make your selects much much faster.

---

this is related to how many data can mysql read during a disk read cycle,
and how many data can be sent over the network in a data sending cycle

when data size growth, more overheads will be on

• disk-read cycle (mysql spent more time on record seeking)
• data-sending (require more cycle to allow data transfer over the network)

not all data are stored in memory especially on text and blob,
mysql need to found the data from disk,
and transfer back to the clients

in another words, mysql index is fast,
because it does not required disk read

---

This is a very wild guess, but this might be a low level implementational issue, MySql doesn't expect you to retrieve so much data at once so it has to reallocate a bigger block of memory for its internal use and copy data from the old location to the new one and repeat this over and over again as the data grows, this is the only thing that comes to my mind that can explain the query time going up exponentially while the data grows linearly. Your solution is to limit the amount of data you retrieve at once.