Scenario of SELECT a text column in MySQL

Question

How does mysql SELECT FROM a table which has one or more TEXT column?

Assume this Query:

SELECT content FROM posts WHERE id = 1

As far as I know, TEXT types store in disk and have a pointer in table. so how this query works in action?
Which scenario is okay?

1 : MySQL loads all text related to this table, then starts to search in table. finds #1, and gets the content and returns row. ( pre loading all TEXT )

2 : MySQL search in table, finds #1, load the specific TEXT related to current matched row from disk and returns row. ( something like lazy loading!! )

Or maybe MySQL works another way!

and how a very large table with TEXT column in it, can reduce SELECT and reading speeds?

Answer 1

When you perform a query and your predicate includes an indexed column. Say your index is a B-Tree on the primary key. then the index/tree is traversed until you reach the pointer to the workload (tuple/row). You will read the whole row from disk.

Id you had a range: id > x and id < y, and if the index was clustered (such that the leaf node of a B-Tree containes the tuples sequentially stored on disk, then it finds the payload of id =x and sequentially scans disk until it reaches id = y - 1.

So I don't see where is the difference if one projected column was TEXT or VARCHAR except the fact when you need an index on that particular column since TEXT has variable size, you might need to limit the index on a sized prefix.

Answer 2

I guess your question is wrong.

and how a very large table with TEXT column in it, can reduce SELECT and reading speeds?

You probably want to increase and not to reduce the reading speed.

If your TEXT fields contain a lot of data, then you can increase the speed by using better hardware and by having the SQL server on the same hardware as your application in order to have no network traffic. The cache of the SQL server should be big. Then the recently used data could be fetched faster because it is stored in RAM memory and not on the harddrive.

Answer 3

If no index can be used to find "id=1", yes, MySQL will load everything before it can find "id=1", as your option 1 unfortunately.

You can test it to see if it is true on your system (different MySQL version/DB Engine may have different results):

1) create a table test1 with two columns fno int data text

2) fill the table with some real large text, with unique fno and text so you can return one row in the following queries.

3) search the table using :

select sql_no_cache len, data from test1 where fno = 10000;
select sql no_cache len, data from test1 where data like 'tex10000%';

You will see their running time are very close. you can re-run them in different order to make sure caching is not effecting the result.

If the MySQL only load the text column after WHERE, the first query should be much faster than the second one since it does not need or load the data column for WHERE.

MySQL server will ask the db Engine to return all columns for WHERE when there is no index can be used. (If an index can be used, MySQL will ask the db Engine to use the index and return the matched rows only, and process further WHERE filtering on those rows if needed).

To solve the problem, you need create an index on fno. you will see the first query will be very faster after the index. If table scan can not be avoided totally and you don't want to search on the text column (data), you can move the text column to another table and link it to the main table using an unique key, like this:

files table: fno fid fname

filedata table: fid data

then do search like:

select fno, data from files left join filedata using (fid) where fname like '/tmp/aaa.txt';